Run relog from the directory where the datacollector file is
Type the following in the command prompt
c:\cd PerfLogs\Admin\FromTemplate\000001
( the above is the path where my file is)
running relog
c:\cd PerfLogs\Admin\FromTemplate\000001 > relog ProdSample.blg -f SQL -o SQL:BaselineData!ProdSample.blg
ProdSample is my perflog file
-f is the format in which you want
-o is the output
SQL:BaselineData ( it is the ODBC connection that I had created pointing to the database where I wanted the data to be dumped)
Once I ran the above command relog transferred the data and it created the following tables in the database connected trough ODBC . BaseLinedata in this case.
DisplayToID
CounterData
CounterDetails
Wednesday, February 18, 2015
Wednesday, February 11, 2015
How do I use parameters in an SSIS ADO NET source?
How do I use parameters in an SSIS ADO NET source -
Question
When I use SQL Command Text, there is a Parameters button in OLE DB Source. But now I want to use ADO NET Source in Data Flow and I cannot find such button in ADO NET source. How do I pass parameters to an ADO NET Source?
Answer
You can use variables together with Expression to pass the parameters:
1) Switch to Control Flow, click the Data Flow Task.
2) In the Properties window, notice a property called Expression and a small button next to it. Click the button to open the Expression Editor.
3) In the Property list, select [The name of ADO NET Source].[SqlComamnd] and click the button under expression column to open Expression Builder.
4) Write the query with variable names and click the Evaluate Expression button to test the expression. For example:
"SELECT * FROM EMPLOYEES WHERE FNAME='" + @[User::FName] + "'"
"SELECT * FROM EMPLOYEES WHERE FNAME='" + @[User::FName] + "'"
Friday, February 6, 2015
Friday, January 16, 2015
script:User Permission
The following script creates a new user and lets the user create new objects and grant permission on the objects to other users
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'user1')
DROP USER [user1]
create user [user1] for login [domain\user1]
EXEC sp_addrolemember db_ddladmin, [user1]; -- allows to create objects
EXEC sp_addrolemember db_securityadmin, [user1]; --allows to assign permission on objects
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'user1')
DROP USER [user1]
create user [user1] for login [domain\user1]
EXEC sp_addrolemember db_ddladmin, [user1]; -- allows to create objects
EXEC sp_addrolemember db_securityadmin, [user1]; --allows to assign permission on objects
Thursday, January 15, 2015
suspect pages errors
USE msdb
GO
SELECT * FROM dbo.suspect_pages
Event_type column gives different values
Errors Recorded in suspect_pages Table
The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows. The following table shows errors logged in the event_type column of the suspect_pages table.
The suspect_pages table also records transient errors. Sources of transient errors include an I/O error (for example, a cable was disconnected) or a page that temporarily fails a repeated checksum test.
How the Database Engine Updates the suspect_pages Table
The Database Engine takes the following actions on the suspect_pages table:
---To clean up the suspect_pages table run the following
delete s
from msdb.dbo.suspect_pages s
where event_type in (4,5,7)
GO
SELECT * FROM dbo.suspect_pages
Event_type column gives different values
Errors Recorded in suspect_pages Table
The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows. The following table shows errors logged in the event_type column of the suspect_pages table.
| Error description | event_type value |
|---|---|
| 823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page (for example, a bad page ID) | 1 |
| Bad checksum | 2 |
| Torn page | 3 |
| Restored (The page was restored after it was marked bad) | 4 |
| Repaired (DBCC, AlwaysOn, or mirroring repaired the page) | 5 |
| Deallocated by DBCC | 7 |
The Database Engine takes the following actions on the suspect_pages table:
- If the table is not full, it is updated for every 824 error, to indicate that an error has occurred, and the error counter is incremented. If a page has an error after it is fixed by being repaired, restored, or deallocated, its number_of_errors count is incremented and its last_update column is updated
- After a listed page is fixed by a restore or a repair operation, the operation updates the suspect_pages row to indicate that the page is repaired (event_type = 5) or restored (event_type = 4).
- If a DBCC check is run, the check marks any error-free pages as repaired (event_type = 5) or deallocated (event_type = 7).
---To clean up the suspect_pages table run the following
delete s
from msdb.dbo.suspect_pages s
where event_type in (4,5,7)
Wednesday, January 14, 2015
Script: List all foreign keys in a database
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
Tuesday, January 13, 2015
Diskspace analysis, database growth (yearly)
I am using the following query to forecast the yearly database growth
select cast(MAX(Space_Used_in_MB)as float) as max_size , min(Space_Used_in_MB) AS MIN_SIZE ,
cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 as [yearly percent growth] ,
Database_Name from SQLMonitor..SQLAdmin_Monitor_db_file_info
where FILE_ID = 1
and Time_collected >='01/13/2014'
group by Database_Name
order by database_name
--SQLMonitor is a user database
--SQLAdmin_Monitor_db_file_info is a user table where a job ( code here) logs the database file sixe in every four hours
-- if the time frame you want to measure is two years change the time_collected to '01/13/2013' and change the yearly percent growth calculation as follows
( cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 )/2
Linear: Current disk space + (growth * No of periods)
Compound: Current disk space * (1 + Growth %) ^ No of periods
Geometric: Current growth + [initial increment * {1-Incrementgrowth rate^ (No of period+1)}]/ (1 – growth rate)
To illustrate the fact, let us take one example. Say for, you have a database and it is of 100 GB. If it grows at 10% per year, then after 2 years, it will be 100 + (10 * 2) = 120 GB (linear growth).
If it is so that the database grows at 5% rate/month for 2 years, then it will be
100 * (1 + 0.05) ^ 24 = 322.5 GB (compound growth).
Take another case. Say for you have already a database of 100GB. Now the growth rate is so that it starts at 10 GB/month and the increment itself increments at 5%/month, then in 24 months, it will be 100 + {(10 * (1- (1.05 ^ (24 + 1))) / (1-1.05)} = 100 + 477 = 577 GB (geometric growth).
select cast(MAX(Space_Used_in_MB)as float) as max_size , min(Space_Used_in_MB) AS MIN_SIZE ,
cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 as [yearly percent growth] ,
Database_Name from SQLMonitor..SQLAdmin_Monitor_db_file_info
where FILE_ID = 1
and Time_collected >='01/13/2014'
group by Database_Name
order by database_name
--SQLMonitor is a user database
--SQLAdmin_Monitor_db_file_info is a user table where a job ( code here) logs the database file sixe in every four hours
-- if the time frame you want to measure is two years change the time_collected to '01/13/2013' and change the yearly percent growth calculation as follows
( cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 )/2
Growth of database ( http://www.sqlservercentral.com/articles/Administration/analyzingdiskcapacityforsqlserver/2467/)
Database growth can be of three types:- linear growth (grows at a constant amount over time)
- compound growth (grows at a constant rate for a specific period of time)
- geometric growth (grows periodic by some increment, which is in turn growing at a constant rate)
Linear: Current disk space + (growth * No of periods)
Compound: Current disk space * (1 + Growth %) ^ No of periods
Geometric: Current growth + [initial increment * {1-Incrementgrowth rate^ (No of period+1)}]/ (1 – growth rate)
To illustrate the fact, let us take one example. Say for, you have a database and it is of 100 GB. If it grows at 10% per year, then after 2 years, it will be 100 + (10 * 2) = 120 GB (linear growth).
If it is so that the database grows at 5% rate/month for 2 years, then it will be
100 * (1 + 0.05) ^ 24 = 322.5 GB (compound growth).
Take another case. Say for you have already a database of 100GB. Now the growth rate is so that it starts at 10 GB/month and the increment itself increments at 5%/month, then in 24 months, it will be 100 + {(10 * (1- (1.05 ^ (24 + 1))) / (1-1.05)} = 100 + 477 = 577 GB (geometric growth).
Subscribe to:
Posts (Atom)