Learn database in a simple and easy way.
Below query can be used to see the growth size of database files. DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE @bfn VARCHAR(1000); DECLARE @efn VARCHAR(10); — Get the name of the current default trace SELECT @filename = CAST(value AS NVARCHAR(1000)) FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2; — rip […]
Continue ReadingSometimes we need to check which tables of our database is highly populated. Below is the T-SQL Script which we can use to find out that. SELECT t.NAME AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) […]
Continue ReadingSometimes we needs to find out the last executed commands on the database server. Below is the SQL script share by one of my friend Ken Watson to find out the last executed queries. SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 1 ORDER BY usecounts DESC; About Ken He […]
Continue ReadingScript to find the Fragmentation in Indexes In my previous articles, Fragmentation in SQL Server and Rebuild And Reorganization of Indexes , we talk in detail about Index fragmentation and rebuild or reorganize the indexes. Below is the SQL script which we can use to find the Fragmentation in Indexes created on the database. SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level, […]
Continue ReadingBelow is the query to find out all the Queries and their details like SSID etc. running on the SQL Server from all databases. It helps in finding out which queries/jobs running on the server. It can be helpful in detecting the blocking queries also. USE Master SET NOCOUNT ON SET ANSI_PADDING ON SET […]
Continue ReadingMany times I was asked how to pass Microsoft certifications and how difficult it is. In my point of view, it simple to crack Microsoft certification exams if you have worked hard on basic concepts and implemented most of the questions and scenarios mention in your study books/blogs on your system. 1) Take out the list […]
Continue Reading