Archive for the ‘SQL Scripts’ Category
Posted by Jugal Shah on March 29, 2011
We often like to check the progess or completed percentage of time consuming command. You can query the sys.dm_exec_requests DMV to check the status of the command.
You must have atleast view state permission to execute the below query.
ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION
SELECT dmr.session_id,
dmr.status,
dmr.start_time,
dmr.command,
dmt.TEXT,
dmr.percent_complete
FROM sys.dm_exec_requests dmr CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt WHERE dmr.command IN (‘ALTER’, ‘Backup’, ‘Restore’, ‘DBCC’, ‘Rollback’, ‘TDE’)
18.520469
73.856621
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Leave a Comment »
Posted by Jugal Shah on March 28, 2011
NT AUTHORITY\SYSTEM login gets created when we install SQL Server. By querying sys.syslogins, we would get an idea of the SQL Server Installation date time as per the creation date time of NT AUTHORITY\SYSTEM.
SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0×010100000000000512000000
SELECT * FROM sys.syslogins WHERE sid = 0×010100000000000512000000
SELECT createdate AS sql_server_install_date FROM sys.syslogins WHERE sid = 0x010100000000000512000000
SELECT * FROM sys.syslogins WHERE sid = 0x010100000000000512000000
18.520469
73.856621
Posted in SQL Scripts, SQL Server | Tagged: SQL Server Installation Date | Leave a Comment »
Posted by Jugal Shah on March 28, 2011
Many times we would like to check OS information and boot time. We can check the operating system properties and reboot time by executing systeminfo command from dos prompt.
C:\>systeminfo
18.520469
73.856621
Posted in OS and SQL, SQL Scripts | Tagged: Operating System | 1 Comment »
Posted by Jugal Shah on March 26, 2011
You can use below script to update the statistics with the FULL Scan. You can pass the database name in below script, I have given JShah as database name.
EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’
You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.
EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’
18.520469
73.856621
Posted in Database, DB Articles, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Update Statistics Script | Leave a Comment »
Posted by Jugal Shah on March 10, 2011
sys.dm_clr_properties: Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR.
Enabling CLR using SP_Configure doesn’t gurantee that it is initialized. To initialize CLR, you have to execute ASSEMBLY statements, CLR Routine Type or Trigger.
See below image for the output of this DMV.
Below are the different state of CLR.
- Mscoree is not loaded.
- Mscoree is loaded.
- Locked CLR version with mscoree.
- CLR is initialized.
- CLR initialization permanently failed.
- CLR is stopped
Check http://msdn.microsoft.com/en-us/library/ms174983.aspx link for different CLR state description.
18.520469
73.856621
Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Check CLR status, sys.dm_clr_properties | 2 Comments »
Posted by Jugal Shah on February 27, 2011
You can use Server Property errorlogfilename to get the error log file path.
SELECT SERVERPROPERTY(‘ErrorLogFileName’)
18.520469
73.856621
Posted in Database, DB Articles, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: SERVERPROPERTY('ErrorLogFileName') | Leave a Comment »
Posted by Jugal Shah on February 3, 2011
sys.dm_os_cluster_nodes
This view returns a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance has been defined.
sys.dm_io_cluster_shared_drives
This view returns the drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.
Permission
You must have VIEW SERVER STATE permission for the SQL Server instance.
SELECT *
FROM sys.dm_os_cluster_nodes
–OR
SELECT *
FROM Fn_virtualservernodes()
–Shared Drives
SELECT *
FROM sys.dm_io_cluster_shared_drives
18.520469
73.856621
Posted in Database, DB Articles, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: http://sqldbpool.com/, Jugal Shah, sys.dm_io_cluster_shared_drives, sys.dm_os_cluster_nodes | Leave a Comment »
Posted by Jugal Shah on January 31, 2011
It is easy to find out the orphaned SQL logins by comparing the SID of SQL Login and User, but what in case of windows login.
Take an example if windows login is dropped and it is still exists in SQL Server. You can find out all the delete windows login which is orphaned in SQL Server, using below procedure.
Sp_validatelogins
Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment.
CREATE TABLE #dropped_windows_logins
(
[sid] VARBINARY(85),
[name] SYSNAME
)
INSERT #dropped_windows_logins
EXEC sys.Sp_validatelogins
SELECT *
FROM #dropped_windows_logins
DROP TABLE #dropped_windows_logins
18.520469
73.856621
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: http://sqldbpool.com/, Jugal Shah, Sp_validatelogins | 2 Comments »
Posted by Jugal Shah on January 31, 2011
You can simply run one of the below query to check the SQL Server last restart time.
SELECT Dateadd(s, ( ( -1 ) * ( osd.[ms_ticks] / 1000 ) ), Getdate()) AS serverrestartdatetime,
osd.sqlserver_start_time
FROM sys.[dm_os_sys_info] osd;
OR
SELECT name,
crdate
FROM sys.sysdatabases
WHERE name = 'tempdb'
18.520469
73.856621
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: SQL Server Start time | Leave a Comment »
Posted by Jugal Shah on January 29, 2011
sys.dm_os_schedulers is a useful DMV to find out the CPU pressure. It returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor.
We can use this DMV to monitor the condition of a scheduler or to identify runaway tasks. It will help us to identify if there is any CPU bottleneck in the SQL Server machine.
We have to check for “runnable_tasks_count” column value which indicates the count of workers that have tasks assigned to them that are just waiting for their share of time on the scheduler (logical CPU). The value of runnable_tasks_count should be as low as possible.
Permission
Requires VIEW SERVER STATE permission on the server
SELECT scheduler_id,
status,
cpu_id,
is_online,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
pending_disk_io_count,
load_factor
FROM sys.dm_os_schedulers
GO
If you find the the avg(runnable_tasks_count) greater then 0 that means system is waiting for CPU time. If Pending_disk_io_count is greater then 0, that means system is bound by IO you need to get disks to perform better.
18.520469
73.856621
Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: CPU bottleneck, http://sqldbpool.com/, Jugal Shah, sys.dm_os_schedulers | Leave a Comment »
Posted by Jugal Shah on January 27, 2011
CREATE DATABASE sqldbpool
–Solution - I
EXEC Sp_renamedb ‘SQLDBPool’, ‘Jugal’
–Solution - II
ALTER DATABASE sqldbpool MODIFY name=jugal
18.520469
73.856621
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | 2 Comments »
Posted by Jugal Shah on January 26, 2011
In SQL Server 2000
sp_configure ‘allow’ ,1
GO
Reconfigure with override
GO
Update sysdatabases set status = status&(~32768) where name = ‘SQLDBPool’
GO
sp_configure ‘allow’, 0
GO
Reconfigure with override
go
IN SQL Server 2005/2008
ALTER DATABASE sqldbpool
SET online
18.520469
73.856621
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: http://sqldbpool.com/, Online, Script | Leave a Comment »
