aiDeeva.com

Category: DB Articles

  • Prevent Truncation of Dynamically Generated Results in SQL Server Management Studio

    Problem
    While working with the Results to Text option in SSMS, you may come across a situation where the output from dynamically generated data is truncated. In this article I will guide you on how to fix this issue and print all the text for the Results to Text option.

    Solution
    http://www.mssqltips.com/sqlservertip/2795/prevent-truncation-of-dynamically-generated-results-in-sql-server-management-studio/

  • Steps to Check the Host Name for a Clustered SQL Server Instance

    Problem
    While troubleshooting a SQL Server cluster failover issue, it is essential to know the time needed for the cluster failover and the node name where SQL Server was running before the failover occurred. In this tip, I will show you the different options to find the failover time and node name where SQL Server was running before the failover over.

    Solution
    http://www.mssqltips.com/sqlservertip/2744/steps-to-check-the-host-name-for-a-clustered-sql-server-instance/

  • Error: Database diagram support objects cannot be installed

    There will be situation while creating the database diagram, you got the below error message.

    Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

    Above error is self explanatory where it is stating that the “set the database owner to valid login” To fix the issue please follow below one of the solution.

    Solution 1:
    Execute the below script by mentioning the database name.

    ALTER AUTHORIZATION ON DATABASE::MentionDatabaseName TO sa
GO

    OR, you can change the owner by executing the below query.

    EXEC sp_changedbowner 'sa'

    Solution 2:
    Right Click on the database -> Database Properties -> click on files page -> change the owner to SA

  • T-SQL Script to identify the data, log and backup drive

    You can use the below script to identify the data, log and backup file drive.

    select 
@@SERVERNAME as svrName,
drivename, 
drivedescription
from
(
select distinct  SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf'
union
select distinct  SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf'
union 
select distinct  SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak'
) tab1

  • Script to find out the database file size, log file size on SQL Server 2000/2005/2008

    Recently I encountered a situation where i have to find out the database file size on SQL Server 2000. Here is the script which you can use to run on SQL Server 2000. Below script will on SQL Server 2005 or SQL Server 2008 as well.

    You can use sys.master_files instead of sysAltfiles in SQL Server 2005 and SQL Server 2008. Please find all the different scripts below.

    --SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where db_name(dbid) = 'master'

--SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where filename like '%.ldf%'

--SQL Server 2005/2008
SELECT DB_NAME(database_id) AS DBName,
Name AS LogicalName,
Physical_Name, (size*8)/1024 SizeMB
Physical_Name, ((size*8)/1024)/1024 SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Master'
GO