This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Wednesday, March 22, 2017
Backup file size
select p.backup_size/POWER(2.0,30.0) ,p.database_name,p.[Server] as 'DBServer', p.physical_device_name,p.last_db_backup_date from (
SELECT
A.[Server],
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description ,
b.database_name
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A
LEFT JOIN
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
and A.[last_db_backup_date] > '03/20/2017'
)
as p
where backup_size is not null
--and database_name = 'mydb' if to find size only for that db
order by database_name
Found the on sqlservercentral . Very cool demo to convert the size
* 250 Gb */
DECLARE @MY_NumberOfBytes NUMERIC(12,0) = 268435456000;
/* bytes */
SELECT
'Bytes' AS UNIT
,@MY_NumberOfBytes AS NUMBER
UNION ALL
/* Kilobytes, divide by 2^10 (1024) */
SELECT
'Kilobytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,10.0) AS NUMBER
UNION ALL
/* Megabytes, divide by 2^20 (1048576) */
SELECT
'Megabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,20.0) AS NUMBER
UNION ALL
/* Gigabytes, divide by 2^30 (1073741824) */
SELECT
'Gigabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,30.0) AS NUMBER
UNION ALL
/* Terabytes, divide by 2^40 (1099511627776) */
SELECT
'Terabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,40.0) AS NUMBER;
Thursday, February 16, 2017
Remap the orphaned database users
I found few database users (SQL users) with no login in the database. These are orphaned users
The following query is run in the database context
-- SQL to run to identify users without login :
SELECT CASE WHEN DATALENGTH(sid) = 28
AND type = 'S' -- only want SQL users
AND principal_id > 4 -- ignore built in users
THEN 1 ELSE 0 END AS is_user_without_login,*
FROM sys.database_principals
--I found the user where is_user_without_login = 1
--dropped the user
--and created the user linking the user to the login
use db_01
GO
drop user dbuser1
create user dbuser1 for login svrlogin1
The following query is run in the database context
-- SQL to run to identify users without login :
SELECT CASE WHEN DATALENGTH(sid) = 28
AND type = 'S' -- only want SQL users
AND principal_id > 4 -- ignore built in users
THEN 1 ELSE 0 END AS is_user_without_login,*
FROM sys.database_principals
--I found the user where is_user_without_login = 1
--dropped the user
--and created the user linking the user to the login
use db_01
GO
drop user dbuser1
create user dbuser1 for login svrlogin1
Wednesday, February 1, 2017
SSIS: Split the file into error file upon error while inserting to the destinaltion table
The SSIS imports a flat file to the SQL table and upon error it writes out the error lines ( including the contents to the a new file e.g. file1_error.txt and inserts the error records ( account number and the row number in this case) to the error ruble in the database. This multiple tasks are possible by the Multicast step.
The script component: Script to update the rowcount is used to count the rows in the source file.
int _RowNumber = 2;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.RowNumber = _RowNumber;
_RowNumber += 1;
}
The script component: Script to update the rowcount is used to count the rows in the source file.
int _RowNumber = 2;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.RowNumber = _RowNumber;
_RowNumber += 1;
}
Friday, January 27, 2017
Keeping the size of the SSISDB to minimum
The SSISDB database size was growing large in the SQL server 2012 machine. I had to clean the SSISDB to bring the size to minimum.
The SSIS catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the SSIS server. When the SSIS catalog is implemented, the SSISDB database is automatically created.
SSIS projects, packages, parameters, environments, and operational history are all stored in the SSISDB database so if you have hundreds of SSIS packages or packages that run every few minutes you could see how the database storing all the historical information would grow exponentially.
Steps I took to reduce the size of SSISDB
Checked that the SQL maintenance job ( created by SSIS Installation) was running, the job name is SSIS Server Maintenance Job
Looking at the first step, SSIS Server Operations Records Maintenance, I notices that it executes a stored procedure named internal.cleanup_server_retention_window. This was the stored procedure that cleans up history
I could also see that the stored procedure queries catalog.catalog_properties to find these values.
use SSISDB
GO
set property_value = 100
from catalog.catalog_properties c
where property_name = 'RETENTION_WINDOW'
The SSIS catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the SSIS server. When the SSIS catalog is implemented, the SSISDB database is automatically created.
SSIS projects, packages, parameters, environments, and operational history are all stored in the SSISDB database so if you have hundreds of SSIS packages or packages that run every few minutes you could see how the database storing all the historical information would grow exponentially.
Steps I took to reduce the size of SSISDB
Checked that the SQL maintenance job ( created by SSIS Installation) was running, the job name is SSIS Server Maintenance Job
Looking at the first step, SSIS Server Operations Records Maintenance, I notices that it executes a stored procedure named internal.cleanup_server_retention_window. This was the stored procedure that cleans up history
I could also see that the stored procedure queries catalog.catalog_properties to find these values.
use SSISDB
GO
I changed the property value of the property name Retention_window to 10 days from 365 ( I could update to this short time frame as this was a dev server)
update c
Then I ran the maintenance job again and the database size reduced.
I found all this information in the following link-
Subscribe to:
Posts (Atom)