SQL Full Backup & Transaction Log backup

REMEMBER: Transaction Log is not automatically truncated when a Full Backup is performed.


To better understand Transaction Log Truncation:


Troubleshot a Full Transaction Log:

SELECT name ,
       recovery_model_desc ,
FROM   sys.databases
WHERE  name = 'MyDatabase'

If the value returned for log_reuse_wait_desc, from the previous sys.databases query, is LOG_BACKUP, then you are operating a database in the FULL recovery model (or less common, the BULK_LOGGED recovery model), without taking transaction log backups.
If the value returned for log_reuse_wait_desc is ACTIVE_TRANSACTION, then you are suffering long-running or uncommitted transactions.
Another common cause of the Active Transaction value for log_reuse_wait_desc is the presence of “orphaned” explicit transactions that somehow never got committed.

To identifying the active transaction:

SELECT st.session_id ,
         st.is_user_transaction ,
         dt.database_transaction_begin_time ,
         dt.database_transaction_log_record_count ,
FROM     sys.dm_tran_session_transactions st
         JOIN sys.dm_tran_database_transactions dt
                   ON st.transaction_id = dt.transaction_id
                      AND dt.database_id = DB_ID('MyDatabase')
WHERE st.session_id = 56

Change the st.session_id value with the SPID value of DBCC OPENTRAN.

Recovery Model Overview:


Posted in Computers and Internet | Tagged , | Leave a comment

Windows 8.1 and SSD disk

Also on Windows 8/8.1 Windows System Assessment Tool is present and used. For Optimizing Windows 8/8.1 to use SSD disk run this command:

WinSAT.exe formal

Check also that your SATA mode is in AHCI to have trim enabled.
Is non necessary to reinstall OS… check this: http://support.microsoft.com/kb/2751461 or boot in SafeMode one time and then reboot.

Via: http://technet.microsoft.com/en-us/library/hh825197.aspx

Posted in Computers and Internet | Tagged | Leave a comment

How to remove dumpfile on ESXi

For clean up ESXi dumpfile on ESXi host, first check where the dumpfile is configured and active:

esxcli system coredump file list

Then remove the file using this command:

esxcli system coredump file remove --force -f [path of dumpfile]
Posted in Computers and Internet | Tagged | Leave a comment

How to Backup ESXi Configuration with PowerCLI

I have 2 ESXi Hosts without vCenter. For backup the ESXi configuration I used a PowerCLI script:

Connect-VIServer [2001:DB8::10]
Connect-VIServer [2001:DB8::11]
$esxhost = Get-VMHost
Get-VMHostFirmware -VMHost $esxhost -BackupConfiguration -DestinationPath C:\Backup

Change the IP Address with the address of yours ESXi hosts and set the DestinationPath according to your environment.

For restore the configuration use these commands:

Connect-VIServer [2001:DB8::10]
$esxhost = Get-VMHost
Set-VMHostFirmware -VMHost $esxhost -Restore -SourcePath C:\Backup\configBundle-[2001:DB8::10].tgz

Remember to put ESXi host in “Maintenance Mode” before restore the conf.

Posted in Computers and Internet | Tagged , , | Leave a comment

How to Batch Redirection

To merge Standard Output and Standard Error and redirect them together to a single file:

Script.cmd > Script.log 2>&1

To redirect success and error messages to separate log files:

Script.cmd > Script.log 2> ScriptErrors.log

Use >CON to send text to the screen, no matter what, even if the batch file’s output is redirected.
This could be useful when prompting for input even if the batch file’s output is being redirected to a file.

Posted in Computers and Internet | Tagged | Leave a comment

How to create backup role on SQL 2008 R2

The minimum rights for backup/restore on SQL server 2008R2 are these:

Server roles:

Database roles:


This is an example of error when some rights are missing when BACKUP EXEC try to backup SQL:

Login failed for user ‘[DOMAIN]\backup_service’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]


Posted in Computers and Internet | Tagged , | 1 Comment

Ubuntu 14.04 and Hyper-V

Ubuntu 14.04 (or better Linux kernel 3.13) can now run on Hyper-V Generation 2 virtual machines.
You only need to disable secure boot (run this on Hyper-V machine):

Set-VMFirmware -VMName "DEV03-Ubuntu" -EnableSecureBoot off

Add to /etc/default/grub on kernel parameters the video size you prefer and the I/O scheduler NOOP for better disk performance:

GRUB_CMDLINE_LINUX="video=hyperv_fb:1440x900 elevator=noop"

and run this command for apply the changes:

sudo update-grub

After installation, remember to install these daemons (run this on Ubuntu virtual machine):

sudo apt-get install hv-kvp-daemon-init

Via: http://technet.microsoft.com/en-us/library/dn531029.aspx

The hv-kvp-daemon-init is deprecated. Install this:

sudo apt-get install Linux-cloud-tools-generic

For copy files from host to VM you can now use powershell:

Copy-VMFile -name Ubuntu-VM -SourcePath "C:\Data\test.txt" -DestinationPath "/home/linuxuser/test.txt" -CreateFullPath -FileSource Host
Posted in Computers and Internet | Tagged , | Leave a comment