Is it good to put data and log files together ?

This is a most common question asked ! The answer to this depends on the environment you work in.
What I mean by that is: If your environment is a heavy read / write then it is good to put data (.mdf) and log files (.ldf) on separate physical disks (preferably RAID). The reason for this is that when there are heavy inserts/updates/deletes, the way SQL server handles is that
1. It writes to the log files that it is going to change the data
2. It writes to the data files
3. It writes to the log file that it has changed the data i.e it commits the transaction.
This shows that it has to write both to the log and data files and the writes are random meaning it writes to the log file then jumps to data file and then again to the log file. This kills the performance making the writes much slower !

There might be good reasons to put data and log files on same disk/drive like
1. If your database is READ-ONLY i.e having more read activity than write like reporting or data warehouse.
2. If there is a lot of use of TEMP-DB only as compared to data / log file drives.
3. If you have a RAID-10 array, its good to put your data and log files on the same drive as RAID-10 is fast and have both mirroring and data striping functionality.

Although, to decide what should be implemented, a thorough research about the environment should be done and when the system is implemented using PERFMON to track the disk activity i.e disk read/writes helps !

Also to change the location of data and log file ALTER DATABASE can be used. **see my previous post how this can be done **

Hope this helps !

Thanks,
\\K

Reporting Services Login box appears when trying to deploy in SSRS 2005 SP3

Hi All,

I installed SQL Server 2005 SP3 recently. After that when I try to
deploy a solution in SSRS 2005, I get a dialog box with title:
"Reporting Services Login". It has my server listed as the first row:
"http://localhost/reportserver" and then it has two text boxes asking
for Username and Password.

Solution/Approach:

When I was deploying the reports, in the properties of the report in target server URL field, I had http://localhost/Reports

I changed it to http://localhost/Reportserver, and everything worked fine.

e.g:
1. Open your project in SQL Server Business Intelligence Development Studio
2. Select the project menu followed by the properties of the project.
3. Change the TargetServerURL: To the appropriate path to your reportserver.(http://localhost/Reportserver)


Hope this helps,

Thanks,

\\K

Moving database from one location to another

Hi All,

Recently I ran across a situation where in there was a GIS application which was trying to feed a large amount of data (more than 22GB) to SQL server. This led to the increase of the data file (.mdf) and eventually this led to filling the disk space. So came the situation of moving the database to a different location where there was more room for growth !!

Solution/Approach:
1. using Attach/detach method.
use adventureworks
go

sp_helpdb 'adventureworks' ---> this will show you the logical and physical file names of the data and the log files for a particula database

Detach the database:

USE master;
GO
EXEC sp_detach_db @dbname = N'AdventureWorks';
GO

Move the files data(.mdf), secondary data file(.ndf) and log files (.ldf) to the desired location.

Attach the database:


USE master;
GO
CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')
    FOR ATTACH;
GO

This method has certain drawback and is not recomended by BOL: "We recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach."

So the recomended method is to use ALTER DATABASE.


1. sp_helpdb 'adventureworks' -> will give logical and physical file names

2. use the alter database command to move the files.

ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_dat, FILENAME = 'E:\adventureworks_database\adventureworks.mdf');
ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_dat2, FILENAME = 'D:\adventureworks_data_file2\adventureworks_dat2.ndf'); --- here if you have any secondary data file, if not ignore this
ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_log, FILENAME = 'E:\adventureworks_database\adventureworks.ldf');


ALTER DATABASE adventureworks SET OFFLINE
WITH ROLLBACK IMMEDIATE --- this will take your database immediately offline

Now move the data files and log file to the respective desired location.

ALTER DATABASE adventureworks SET ONLINE -- this will take your database online

This will move your database from one location to another and is more efficient way to do.

This is a good link to follow: http://www.sqlservercentral.com/articles/Administration/65896/

Hope this helps !!

\\K

SQL2005 [264] An attempt was made to send an email when no email session has been established.

Hi All,

Below is the problem which is more obvious to face when a fresh install of SQL server 2005 is done.

Problem: "[264] An attempt was made to send an email when no email session has been established." This message is logged in the SQL agent error logs.

The message is from a job which will send email to an operator when it is completed. In the operator notification setting, I the correct email address has been entered for the 'Email Name' text box, such as kkk@xyz.com
However the Database Mail is configured and I can send/receive a test email.

Solution/Approach:

1. In Management Studio, right-click on SQL Server Agent and select "Properties."
2. Click "Alert System"
3. Uncheck "Enable mail profile"
4. Click "OK"
5. right-click SQL Server Agent again and select "Properties."
6. click "Alert System"
7. Check "Enable mail profile"
8. Click "OK"
9. Restart SQL Server Agent. (this will not bring any thing down, unless jobs are running at the moment when SQL agent is restarted i.e the running jobs will fail, but they can be ran after the agent is restarted)

Hope this helps !!!

\\K

SSIS package fails when scheduled as SQL server agent job

Problem: When run through the BIDS or Visual Studio, the SSIS package runs well. But when scheduled as a SQL agent job, the SSIS package fails with the error message:
" Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. "

Solution: It took me a while to figure out the problem and the same was mentioned at SSC.
The standard security on an SSIS package is "EncryptSensistiveWithUserKey" which only allows the user that created it to be able to run in as long as there is sensitive information (passwords) stored within the package.

A workaround that I am using is that I changed the package security to "EncryptAllWithPassword" which protects the entire package with a password. When you set the package as a job step, go into the properties of that job step, click on the "Command Line" tab and you should be prompted for the password. After you enter the password, the /DECRYPT switch should show up in the command line and the job should then be able to run under a different user.

Users won't be able to see the password because they will need to enter it before viewing the command line for the job step.
(The issue is addressed nicely with many inputs at http://www.sqlservercentral.com/Forums/Topic349385-148-1.aspx)

Installing and configuring the ArcSDE service for SQL server is an easy job, but sometimes the ArcSDE service which is dependent on SQL server stops due to server restart or due to user shutdown. This happens even though the ArcSDE service is set to automatic start in case there is a server reboot.

Problem: The ArcSDE 9.3 service running on SQL server cannot be started. When attempted to start from GUI (cmd ->services.msc -> rt click esri_sde service -> start), it will show an error message that "the ArcSDE service started and stopped. This may happen due to the fact that some services may stop as they have no work to do"

Solution/Approach:

1. run -> cmd -> sdeservice -o list (this will list the status of the sde service running)
Note: to see the status of the service use ------ sdemon -o status -i 5151 ------
2. sdemon -o start
Provide the sde password that was created during the ArcSDE installation.

This will start the service !!!

I came across a very minor issue, but often not scrutinized and this can lead to major trouble !!

Problem: After the SP3 installation, the report server was not available to the application accessing it. i.e the user (report user: it is a fixed user used to pull out reports -- user1) was denied access to the database.

solution:

1. login to the server where your report server is installed.

2. go to Administrative tools --> IIS manager --> on left part Expand local computer --> websites --> default websites --> reports --> rt click --> properties --> DIRECTORY SECURITY --> Authentication and access control --> EDIT --> CHECK THE CHECK BOX at top part [enable annonymous access]

This happens during the installation of service pack and the ""enable annonymous access"" becomes unchecked !!!


This fixes the issue !!