Script: To find the size of the tables in a database

SELECT
table_name = SCHEMA_NAME(o.[schema_id]) + '.' + o.name

, data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(8,4))

FROM sys.objects o

JOIN (




SELECT
p.[object_id]

, total_rows = SUM(p.[rows])

, total_pages = SUM(a.total_pages)

, usedpages = SUM(a.used_pages)

, pages = SUM(




CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0

WHEN a.[type]! = 1 AND p.index_id < 2 THEN a.used_pages

WHEN p.index_id < 2 THEN a.data_pages ELSE 0




END
)
FROM sys.partitions p

JOIN sys.allocation_units a ON p.[partition_id] = a.container_id

LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]

GROUP BY p.[object_id]

) do ON o.[object_id] = do.[object_id]

WHERE o.[type] = 'U'

CPU consumption

--from SQLAuthority website
--SQL SERVER – Who is consuming CPU on my SQL Server?

WITH DB_CPU_Statistics




AS


(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY (SELECT CONVERT(INT, value) AS [DatabaseID]

FROM sys.dm_exec_plan_attributes(qs.plan_handle)

WHERE attribute = N'dbid') AS pa

GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking],

[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],

CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]

FROM DB_CPU_Statistics

WHERE DatabaseID <> 32767 -- ResourceDB

ORDER BY [CPU Ranking] OPTION (RECOMPILE);

Work Adventure: Fix Orphaned user

When I restored a database to a new server, the users were imported but the login were not. The database users were  imported as the orphan users in SQL 2012

Here is how I found the orphaned users in a database

EXEC sp_change_users_login @Action='Report'

--OR we can run the following in the current database context

select * from sysusers where issqluser = 1 and (sid <> 0x0

and sid is not null)

and (LEN(sid) <= 16 ) AND suser_sname(sid) is null 





-----------------------------------------

The in order to link the orphan user to the existing login  I did the following


sp_change_users_login 'Update_One', 'OrphanUser, 'Logintolinkto'

 

Work Adeventure: Backup database to network

I had a situation where I had to perform a backup of the database on a network. I performed the following

Mapped the  nwMachine\L share to Y drive.
Thus I had (\\nwMachine\L) Y:

If I do a backup at this point the SQL server will not see the mapped drive (Y:) yet .
I had to run few scripts to make SQL server see the mapped Y drive.

EXEC sp_configure 'advanced', 1

RECONFIGURE WITH override

GO

--to enable the xp_cmdshell
--once the job is done make sure this is turned off

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE WITH override

GO


now make sql server see the mapped drive
EXEC xp_cmdshell 'net use Y: \\nwMachine\L
GO



--EXEC xp_cmdshell 'net use <local mapped drive> (space)  <shared path>
 
--Once we get result as "command ran successfully" we are successful.

--Now in the backup wizard the Y drive should appear along with the other local drives.

--now turn off the xp_cmdshell

EXEC sp_configure 'xp_cmdshell',0

RECONFIGURE WITH override

GO