[SQL] Get sql server version (script)

How to know the sql server version you are running.


SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR)

[SQL] Script to estimate backup time

How to estimate the time needed to backup a large database.

1. Trigger you backup first.
2. Run the below script to check the time remaining to complete the backup ( Result will be in minutes)

SELECT command, 'EstimatedEndTime' = Dateadd(ms,estimated_completion_time,Getdate()),'EstimatedSecondsToEnd' = estimated_completion_time / 1000,'EstimatedMinutesToEnd' = estimated_completion_time / 1000 / 60,'BackupStartTime' = start_time,'PercentComplete' = percent_completeFROM sys.dm_exec_requestsWHERE command IN ('BACKUP DATABASE','RESTORE DATABASE')

[SQL] Change default sql error log file path.

How to move the default log files placed in: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG to a different location

This may not be required all the times but say on the partition where these log files are stored need few more disk space to freeup, also if you have enabled to keepup more than 6 error log files online.
Simple, for the error log opn SQL Server Configuration Manager, choose the relevant SQL Server services and on the right hand pane go to the advanced tab. Goto startup parameters, you will see the path after -e then change it to required directory, ensure to restart SQL Server services once this change has been affected.

[MOSS-2007]Provisioning for Shared Services Provider 'Shared Service 1' has failed

Issue :
Provisioning for Shared Services Provider 'Shared Service 1' has failed and will be retried. Reason: Windows NT user or group 'domain\username' not found. Check the name again.

Resolution : (This solution helped me)
stsadm -o updatefarmcredentials -userlogin domain\username -password swarajtestpwd