In this blog you will find various tools and articles that are useful to any DBAs. I will try to put more practical articles while at the same time post any database software bugs and some important tips on internals of sql server and optimization. Address cons and pros of different relational database management in use and new developments. This blog will also cover the market perception of different database system including some hard facts, reviews and benchmarks. Teshome Asfaw
Monday, November 24, 2008
Name value pair part II
The article (name value pair part II ) that I published on SQLServerCentral.com is now pulished on best of SQL server central 2008 e-book and can be downloaded from Red-gate software for free.
Monday, September 15, 2008
Issues dropping distribution database
From time to time I came accross when using EM will not completely disable replication leaving distribution database. When you try to drop this database you will get the following error.
Cannot drop the distribution database 'distribution' because it is currently in use. The way to get around is to follow the following steps
USE master
GO
EXEC sp_configure 'allow updates', '1'
RECONFIGURE with override
GO
update master.dbo.sysdatabases
set category = 0
where dbid =
go
EXEC sp_configure 'allow updates', '0'
RECONFIGURE with override
GO
drop database
Cannot drop the distribution database 'distribution' because it is currently in use. The way to get around is to follow the following steps
USE master
GO
EXEC sp_configure 'allow updates', '1'
RECONFIGURE with override
GO
update master.dbo.sysdatabases
set category = 0
where dbid =
go
EXEC sp_configure 'allow updates', '0'
RECONFIGURE with override
GO
drop database
Thursday, September 04, 2008
SQL Server 2008
SQL server 2008 is now shipping !!. To sum up waht is new in SQL server 2008 ?
The following are some that I have come accross.
- Automatic Recovery of Data Pages
- Log Stream Compression
- Resource Governor
- Predictable Query Performance
- Data Compression
- Hot Add CPU
- Policy-Based Management
- Streamlined Installation
- Performance Data Collection
- Language Integrated Query (LINQ)
- ADO.NET Object Services to simplify applicaton development
- DATE/TIME (Date, Time, Datetimeoffset and datatime2) data type
- HIERARCHY ID
- FILESTREAM Data
- Integrated Full Text Search
- Sparse Columns (yes new addition)
- Large User-Defined Types
- Spatial Data Types
- Backup Compression
- Partitioned Table Parallelism
- Star Join Query Optimizations
- Grouping Sets
- Change Data Capture
- MERGE SQL Statement (I have waited for long for this)
- SQL Server Integration Services (SSIS) Pipeline Improvements
- SQL Server Integration Services (SSIS) Persistent Lookups
- Analysis Scale and Performance
- Block Computations
- Writeback (on OLAP)
- Enterprise Reporting Engine
- Internet report deployment
- Manage Reporting Infrastructure
- Report Builder Enhancements
- Forms Authentication Support
- Report Server Application Embedding
- Microsoft Office Integration
- Predictive Analysis
The following are some that I have come accross.
- Automatic Recovery of Data Pages
- Log Stream Compression
- Resource Governor
- Predictable Query Performance
- Data Compression
- Hot Add CPU
- Policy-Based Management
- Streamlined Installation
- Performance Data Collection
- Language Integrated Query (LINQ)
- ADO.NET Object Services to simplify applicaton development
- DATE/TIME (Date, Time, Datetimeoffset and datatime2) data type
- HIERARCHY ID
- FILESTREAM Data
- Integrated Full Text Search
- Sparse Columns (yes new addition)
- Large User-Defined Types
- Spatial Data Types
- Backup Compression
- Partitioned Table Parallelism
- Star Join Query Optimizations
- Grouping Sets
- Change Data Capture
- MERGE SQL Statement (I have waited for long for this)
- SQL Server Integration Services (SSIS) Pipeline Improvements
- SQL Server Integration Services (SSIS) Persistent Lookups
- Analysis Scale and Performance
- Block Computations
- Writeback (on OLAP)
- Enterprise Reporting Engine
- Internet report deployment
- Manage Reporting Infrastructure
- Report Builder Enhancements
- Forms Authentication Support
- Report Server Application Embedding
- Microsoft Office Integration
- Predictive Analysis
Thursday, June 26, 2008
SQL Server 2005 cloning
I have heard a lot on cloning oracle E-business suite. But not SQL Server. Microsoft still lags behind with the idea of cloning. But, I have noticed on Kalen Delaney blog how to do at least some sort of cloning. It is useful that you can script you database with statistics and histogram and able to re-run your execustion plan without loading actaul data. To see detailed info on this visit Kalen's blog on SQL server 2005 Cloning
Friday, June 13, 2008
EAV (name value pair)
part II of EAV or name value pair is now published on SQL Server centeral. Part II will see how to improve a name value pair database that has been implmented to do what a normalised database system should do.
The article will try to address the main issues that you face when using name value pair such as scalability and difficulty in getting a record out of a database.
To view full article you can go to Name value pair part II
The article will try to address the main issues that you face when using name value pair such as scalability and difficulty in getting a record out of a database.
To view full article you can go to Name value pair part II
Name value Pair
I have published a new article on name value pair on SQL server central (http://www.sqlservercentral.com/). Part I of the article discusses about the benefits , drawbacks and perception from different angles .
To view the article click http://www.sqlservercentral.com/articles/Database+Design/62386/
To view the article click http://www.sqlservercentral.com/articles/Database+Design/62386/
Thursday, May 15, 2008
TempDB orignial and current file size
The following script will help you in finding the file size of current tempdb and the size when SQL server last restarted.
SELECT
alt.filename
,alt.name
,alt.size * 8.0 / 1024.0 AS originalsize_MB
,files.size * 8.0 / 1024.0 AS currentsize_MB
FROM
master.dbo.sysaltfiles alt INNER JOIN tempdb.dbo.sysfiles files ON
alt.fileid = files.fileid
WHERE
dbid = db_id('tempdb')
AND alt.size <> files.size
SELECT
alt.filename
,alt.name
,alt.size * 8.0 / 1024.0 AS originalsize_MB
,files.size * 8.0 / 1024.0 AS currentsize_MB
FROM
master.dbo.sysaltfiles alt INNER JOIN tempdb.dbo.sysfiles files ON
alt.fileid = files.fileid
WHERE
dbid = db_id('tempdb')
AND alt.size <> files.size
Saturday, March 15, 2008
Free Tools for the SQL Server DBA
Knowing what you don't know will always save you money. Before buying any tool you should consider asking a question, can I get a free tool for my requirement. Search around and analyse . To my surprise I found some companies brand their tool as free but after downloading it turns out to be not free rather free trial version. Don't put off by this. I have done it previously.
Always begin with an assumption that there is free tool that you could use. Do your research. It will save your company a lot of money and will make you a supremo.
Note also some of this free tools may not be user friendly. To mention just one SQLIO from Microsoft. It is not a tool that you just click and go. But have used it many times and very satisfied with the result that I got.
In general, free tools some times can compare with the tools that you spend a lot of money to do the same thing.
In addition, I came across an article written byDavid Bird for SQL server central and it will cover some of free tools that you will be able to use.
Always begin with an assumption that there is free tool that you could use. Do your research. It will save your company a lot of money and will make you a supremo.
Note also some of this free tools may not be user friendly. To mention just one SQLIO from Microsoft. It is not a tool that you just click and go. But have used it many times and very satisfied with the result that I got.
In general, free tools some times can compare with the tools that you spend a lot of money to do the same thing.
In addition, I came across an article written byDavid Bird for SQL server central and it will cover some of free tools that you will be able to use.
Monday, January 21, 2008
Distribution failure due to Data type differences
Recently I have faced with an issue were the published article column type is different from the subscriber column type. One of the publication articles column data type was varchar and the subscriber table (destination object ) data type was integer. The data in source object support to have a value similar to 012345 where the first character was the number zero and it didn't created any failures until somebody actually put in a value of C12345. This has lead to fail the replication. The only way to get around this was to delete the actual transaction from distribution database.
I used the following steps to do this:
use distributiondb
1. Get an article Id from MSarticles table
2. run exec sp_browsereplcmds @article_id = 'article Id'
3. get the xact_seqno of the article that caused this issue
4. delete the transaction from MSrepl_transactions.
- delete from MSrepl_transactions where xact_seqno =
5. delete the replication commands from MSrepl_commands
- delete MSrepl_commands where xact_seqno =
6. rerun the distribution agent
7. fix the route cause
Note that there is only one entry in MSrepl_transactions while you may have more than one entries in MSrepl_commands.
I used the following steps to do this:
use distributiondb
1. Get an article Id from MSarticles table
2. run exec sp_browsereplcmds @article_id = 'article Id'
3. get the xact_seqno of the article that caused this issue
4. delete the transaction from MSrepl_transactions.
- delete from MSrepl_transactions where xact_seqno =
5. delete the replication commands from MSrepl_commands
- delete MSrepl_commands where xact_seqno =
6. rerun the distribution agent
7. fix the route cause
Note that there is only one entry in MSrepl_transactions while you may have more than one entries in MSrepl_commands.
Monday, January 14, 2008
SQL Server 2000 virtual server install fails
Recently, I have tried to install SQL Server 2000 virtual server on windows server 2003 and the installation fails. I have installed numerous installations on windows 2000 server and never seen such an error. Tried to Google, went to various news groups and have been thinking of what it could be when I have discovered that this is a known issue that has been reported by Microsoft. For details of how to solve this issue go to
Microsoft help and support page
Microsoft help and support page
Subscribe to:
Posts (Atom)