-- I end up using this one a lot. You can add it into a JOIN to filter out tables that are empty.
SELECT O.object_id
, SCHEMA_NAME(O.schema_id) [Schema]
, O.name [Table]
, SUM(P.Rows) [RowCount]
, O.modify_date
, O.create_date
FROM sys.objects O
JOIN sys.partitions P
ON O.object_id = P.object_id
WHERE O.type = 'U'
AND O.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY O.object_id, O.name, O.create_date, SCHEMA_NAME(O.schema_id)
, O.modify_date
HAVING SUM(P.Rows) > 0
-- For example:
DECLARE @VAR VARCHAR(100)
SET @VAR = 'Descp' -- replace the value between the single quotes with your search param
SELECT T.name [TABLE], C.name [Column], O.[RowCount]
FROM sys.tables T
JOIN sys.columns C
ON C.object_id = T.object_id
JOIN (
SELECT O.object_id
, O.name [Table]
, SUM(P.Rows) [RowCount]
, O.modify_date
, O.create_date
FROM sys.objects O
JOIN sys.partitions P
ON O.object_id = P.object_id
WHERE O.type = 'U'
AND O.is_ms_shipped = 0x0
AND index_id < 2
GROUP BY O.object_id, O.name, O.create_date, SCHEMA_NAME(O.schema_id), O.modify_date
HAVING SUM(P.Rows) > 0) O
ON O.object_id = T.object_id
WHERE C.name LIKE '%' + @VAR + '%'
The Adventures of a SQL Server DBA
Tips on T-SQL, SQL Server DBA stuff, and more!
Monday, July 2, 2018
Thursday, June 21, 2018
Delete Older Backup History from msdb Database
-- Gets the oldest backup date in the backupset table
SELECT MIN(backup_finish_date)
FROM msdb.dbo.backupset
-- Gets the newest backup date in the backupset table
SELECT MAX(backup_finish_date)
FROM msdb.dbo.backupset
-- Purges the backupset table of all records BEFORE the one below in quotes
USE msdb;
GO
EXEC sp_delete_backuphistory '6/1/18';
Friday, March 2, 2018
List all Stored Procedures on a SQL Server that get Executed
-- =============================================
-- Author: K Griffith
-- Create date: 09 Feb 2015
-- Description: Returns a listing of all SPs on the server that get used
-- Modify date: 02 Mar 2018
--=============================================
SELECT st.dbid [DB_ID]
, DB_NAME(st.dbid) [Database]
, OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema]
, OBJECT_NAME(st.objectid,dbid) [StoredProcedure]
, MAX(cp.usecounts) [ExecutionCount]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL
AND cp.objtype = 'proc'
AND st.dbid = 7
GROUP BY st.dbid
, DB_NAME(st.dbid)
, OBJECT_SCHEMA_NAME(objectid,st.dbid)
, OBJECT_NAME(objectid,st.dbid)
--, cp.plan_handle
ORDER BY [Database]
Friday, February 23, 2018
Splitting a Full Name Apart When It Has Been Stuffed into a Single Field
-- In case the need arises to split a comma-delimited name - for example, where the last and first name have been stuffed into the same field such as 'Lastname, Firstname'
SELECT [UserName]
, RTRIM(LTRIM(LEFT([FieldName], CHARINDEX(',', [FieldName]) -1))) [LastName]
, RTRIM(LTRIM(STUFF([FieldName], 1, CHARINDEX(',', [FieldName]), ''))) [FirstName]
FROM [DatabaseName].[dbo].[TableName]
Wednesday, July 9, 2014
Setting Up a New SQL Server 2008 R2 Cluster - Make SURE that the Cluster Object has Rights in Active Directory!
Being a DBA is something akin to being a duck-billed platypus: people try to classify you, but you really end up fitting into multiple categories. A DBA ends up doing lots of different things: writing SQL scripts, setting up jobs, restoring backups, and even some server administration.
At work, we normally have the vendor set up things like SQL Server clusters and we manage them after they are set up and running. For reasons unknown to me at the time, we had a vendor try and fail 3 times to set up a SQL Server cluster for us. The job then fell on me. I was a little apprehensive at first because LUNs, SAN space, etc are foreign concepts to me, but I said I would give it a try. I got really lucky. In his previous position, our network engineer was a server guy and had installed a SQL Server cluster from scratch - so I had some great wisdom and input from him. I was able to Google a great deal of what to do and, of course, I had our network engineer in the cube in front of me. Honestly, everything went seamlessly until I would get to the end of installing the first instance. It failed over and over again and we could not figure out why.
This may be something of a perfect storm because it involves Active Directory and AD here at my workplace may run differently than AD at your workplace, but I learned several things in this process. One is that when we are creating a SQL Server cluster, the cluster itself is an object in AD. What we finally found out was happening was that our newly created SQL cluster object needed to create other objects in our domain to complete the install and configuration, but it did not have the rights to do so in AD. You can be logged in as Domain Admin all day long and it will not make a difference unless the cluster object that you have created has the appropriate level of rights in AD. This one bit me good (and several times!) so I am making sure that I make note of it and underline it =)
At work, we normally have the vendor set up things like SQL Server clusters and we manage them after they are set up and running. For reasons unknown to me at the time, we had a vendor try and fail 3 times to set up a SQL Server cluster for us. The job then fell on me. I was a little apprehensive at first because LUNs, SAN space, etc are foreign concepts to me, but I said I would give it a try. I got really lucky. In his previous position, our network engineer was a server guy and had installed a SQL Server cluster from scratch - so I had some great wisdom and input from him. I was able to Google a great deal of what to do and, of course, I had our network engineer in the cube in front of me. Honestly, everything went seamlessly until I would get to the end of installing the first instance. It failed over and over again and we could not figure out why.
This may be something of a perfect storm because it involves Active Directory and AD here at my workplace may run differently than AD at your workplace, but I learned several things in this process. One is that when we are creating a SQL Server cluster, the cluster itself is an object in AD. What we finally found out was happening was that our newly created SQL cluster object needed to create other objects in our domain to complete the install and configuration, but it did not have the rights to do so in AD. You can be logged in as Domain Admin all day long and it will not make a difference unless the cluster object that you have created has the appropriate level of rights in AD. This one bit me good (and several times!) so I am making sure that I make note of it and underline it =)
Location:
Columbus, GA 31904, USA
Friday, October 4, 2013
Reboot Windows 2000, XP, and Vista from a Command Prompt
I came across a situation today where I needed to reboot an ancient XP workstation. However, since I was remoted into it, it did not give me that option in the start menu (at least, I am guessing that is why I did not see the option). At any rate, I was able to Google this and it was a great solution. Open up a command prompt. Type:
SHUTDOWN -r -t 01
Then press Enter. In a dozen years of working in technology, I never had occasion to do this. The great thing about technology is that I am always learning new things =)
SHUTDOWN -r -t 01
Then press Enter. In a dozen years of working in technology, I never had occasion to do this. The great thing about technology is that I am always learning new things =)
Labels:
Windows Server Administration
Location:
Columbus, GA 31904, USA
Monday, August 26, 2013
Querying the Default Trace File
It has always been a bit of a pain having to open up a SQL Server trace file with SQL Server Profiler and try to find the information I am seeking. It's true that the trace can be saved to a table and queried, but this method is SO EASY. It is also great for auditing if you do not have SQL Server 2008 R2 Enterprise installed (We run SQL Server 2008 R2 Standard here at work).
There are several ways to run this query. Here is one that I am currently using in production to track any configuration changes:
DECLARE @TRC_PATH VARCHAR(500)
SELECT @TRC_PATH = CONVERT(VARCHAR(500), value)
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
SELECT TEXTData, HostName, ApplicationName, DatabaseName, LoginName, SPID, StartTime
, EventSequence
FROM fn_trace_gettable(@TRC_PATH,1) fn
WHERE TEXTData LIKE '%configure%'
AND SPID<>@@spid
ORDER BY StartTime DESC
Of course you can change things around to suit your own needs - different fields in the SELECT, change the conditions of the WHERE clause, etc.
FYI, I got this info from this great, great article available at mssqltips.com: http://www.mssqltips.com/sqlservertip/2364/capturing-and-alerting-on-sql-server-configuration-changes/
There are several ways to run this query. Here is one that I am currently using in production to track any configuration changes:
DECLARE @TRC_PATH VARCHAR(500)
SELECT @TRC_PATH = CONVERT(VARCHAR(500), value)
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
SELECT TEXTData, HostName, ApplicationName, DatabaseName, LoginName, SPID, StartTime
, EventSequence
FROM fn_trace_gettable(@TRC_PATH,1) fn
WHERE TEXTData LIKE '%configure%'
AND SPID<>@@spid
ORDER BY StartTime DESC
Of course you can change things around to suit your own needs - different fields in the SELECT, change the conditions of the WHERE clause, etc.
FYI, I got this info from this great, great article available at mssqltips.com: http://www.mssqltips.com/sqlservertip/2364/capturing-and-alerting-on-sql-server-configuration-changes/
Labels:
SQL Server Administration,
T-SQL
Location:
Columbus, GA 31904, USA
Subscribe to:
Posts (Atom)