default database directory

Is it possible like when I create the database so it creates the database in
C:\Data folder and log file at D:\log folder by default. I think we can do
some thing in model database, if yes then can any one let me know how can I
do this ?
thanks
See a post which was open a few days ago:
http://groups.google.de/groups?hl=de...le.com%26rnum%
3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
news:%23lYGo9QRFHA.600@.TK2MSFTNGP10.phx.gbl...
> Is it possible like when I create the database so it creates the database
> in
> C:\Data folder and log file at D:\log folder by default. I think we can do
> some thing in model database, if yes then can any one let me know how can
> I
> do this ?
> thanks
>
>
|||Thanks
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OrLOTBRRFHA.356@.TK2MSFTNGP14.phx.gbl...
> See a post which was open a few days ago:
>
http://groups.google.de/groups?hl=de...0042.191fe 23
8%40posting.google.com&rnum=1&prev=/groups%3Fq%3Ddefault%2Bdirectory%2Bchang
e%2Bsql%2Bserver%26hl%3Dde%26lr%3D%26selm%3D8ef90b 6e.0504130042.191fe238%254
0posting.google.com%26rnum%3D1[vbcol=seagreen]
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:%23lYGo9QRFHA.600@.TK2MSFTNGP10.phx.gbl...
database[vbcol=seagreen]
do[vbcol=seagreen]
can
>

Default data/log path

Hi,

Is there a SQL stored procedure or command to retrieve the server's default path for data and transaction log files?

Thanks.

Part of the Profiler trace created by opening the New Database dialog:

declare @.RegPathParams sysname

declare @.Arg sysname

declare @.Param sysname

declare @.MasterPath nvarchar(512)

declare @.LogPath nvarchar(512)

declare @.ErrorLogPath nvarchar(512)

declare @.n int

select @.n=0

select @.RegPathParams=N'Software\Microsoft\MSSQLServer\MSSQLServer'+'\Parameters'

select @.Param='dummy'

while(not @.Param is null)

begin

select @.Param=null

select @.Arg='SqlArg'+convert(nvarchar,@.n)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @.RegPathParams, @.Arg, @.Param OUTPUT

if(@.Param like '-d%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.MasterPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

else if(@.Param like '-l%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.LogPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

else if(@.Param like '-e%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.ErrorLogPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

select @.n=@.n+1

end

SELECT

@.MasterPath AS [MasterDBPath],

@.LogPath AS [MasterDBLogPath]

Default data and log file location

How can I determine the default data and log file locations of a Smo.Server instance?

For example, my server's default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Data"

Hi,

you can use the following code to get the registry values, but the documentation is not right at this point as I won′t bring back any string if not set in the registry:

Server s = new Server(".");

Console.WriteLine(s.Settings.DefaultFile);

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks - thats what I was looking for. When the registry value is not set I will just grab the file location of the master db:

'open the master database

Dim master As Database = New Database(_server, "master")

master.Refresh()

'get the location of its mdf file

Dim masterMdfFilename = master.FileGroups.Item(0).Files.Item(0).FileName

Console.WriteLine(System.IO.Path.GetDirectoryName(masterMdfFilename))

Default data and log file location

How can I determine the default data and log file locations of a Smo.Server instance?

For example, my server's default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Data"

Hi,

you can use the following code to get the registry values, but the documentation is not right at this point as I won′t bring back any string if not set in the registry:

Server s = new Server(".");

Console.WriteLine(s.Settings.DefaultFile);

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks - thats what I was looking for. When the registry value is not set I will just grab the file location of the master db:

'open the master database

Dim master As Database = New Database(_server, "master")

master.Refresh()

'get the location of its mdf file

Dim masterMdfFilename = master.FileGroups.Item(0).Files.Item(0).FileName

Console.WriteLine(System.IO.Path.GetDirectoryName(masterMdfFilename))

Decreasing the maximum space allocated to the Databse

Hello All,
I have My databse on SQL Server at the Web with an space allocation of 800
MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On Uploading t
he Data in the Database. This has caused the maximum size allocated
to my Database exceed, which disallows me for further Uploading of data. To
Come out of the Problem I opted for taking the regular backup of the databas
e which would truncarte the Log fle, But still I cant decrease the
maximum size of the Log file since almost 300 MB is used wile doing transact
ion for the 500 MB datafiles. I also tried deleting the old/unused data fro
m the datafile , but this did not resulted in the decrease of size of
the datafiles. is there any way I can decrease the maximum space allocated t
o the database. I can afford to deleate certain History data which is of no
use to me.
Regards
JaiHi,
1. Perform the Transaction log backup ( BACKUP LOG DBNAME to
disk='c:\backup\dbname.TRN')
2. Shrink the database (Refer Books online for DBCC SHRINKDATABASE and DBCC
SHRINKFILE)
3. Before doing the step 2, Better make the database to restricted mode
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
DBCC SHRINKDATABASE oR DBCC SHRINKFILE
Have a look into the below article on shrinking the Transaction log file,
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Jai" <jai.s@.sagainfotech.com> wrote in message
news:4805D811-29D2-4973-ABD8-51CD36F9A1EB@.microsoft.com...
> Hello All,
> I have My databse on SQL Server at the Web with an space allocation of
800 MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On
Uploading the Data in the Database. This has caused the maximum size
allocated
> to my Database exceed, which disallows me for further Uploading of data.
To Come out of the Problem I opted for taking the regular backup of the
database which would truncarte the Log fle, But still I cant decrease the
> maximum size of the Log file since almost 300 MB is used wile doing
transaction for the 500 MB datafiles. I also tried deleting the old/unused
data from the datafile , but this did not resulted in the decrease of size
of
> the datafiles. is there any way I can decrease the maximum space allocated
to the database. I can afford to deleate certain History data which is of no
use to me.
>
> Regards
> Jai|||Thanks for the reply..
Will you explain me that even if I delete substantial records from the data
base..why I did not get any decrease in the size of the mdf file ?
Regards,
Jai
-- Hari Prasad wrote: --
Hi,
1. Perform the Transaction log backup ( BACKUP LOG DBNAME to
disk='c:\backup\dbname.TRN')
2. Shrink the database (Refer Books online for DBCC SHRINKDATABASE and DBCC
SHRINKFILE)
3. Before doing the step 2, Better make the database to restricted mode
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
DBCC SHRINKDATABASE oR DBCC SHRINKFILE
Have a look into the below article on shrinking the Transaction log file,
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Jai" <jai.s@.sagainfotech.com> wrote in message
news:4805D811-29D2-4973-ABD8-51CD36F9A1EB@.microsoft.com...
> Hello All,
800 MB (500 MB for Datafile and 300 MB for the LOG file ). I keep On
Uploading the Data in the Database. This has caused the maximum size
allocated
> to my Database exceed, which disallows me for further Uploading of data.
To Come out of the Problem I opted for taking the regular backup of the
database which would truncarte the Log fle, But still I cant decrease the
> maximum size of the Log file since almost 300 MB is used wile doing
transaction for the 500 MB datafiles. I also tried deleting the old/unused
data from the datafile , but this did not resulted in the decrease of size
of
> the datafiles. is there any way I can decrease the maximum space allocated
to the database. I can afford to deleate certain History data which is of no
use to me.
> Jai

decrease the size of Sql backup files

Hi,
I am looking for sql tool that it could shrink the sql data backup and log
files size.
I know one software called litespeed. There is any more good software that
could do the same function as litespeed does?
Thanks,Sonasafe by SonaSoft
SQL Safe by Idera
SQL Backup by Red Gate Software|||thanks,
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Ob9yTYNLGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am looking for sql tool that it could shrink the sql data backup and log
> files size.
> I know one software called litespeed. There is any more good software that
> could do the same function as litespeed does?
> Thanks,
>

Decrease the size of a Sql 2000

i had set the size of a new database to 5 GB and transactional log file to 50
MB. Now i realized that the database would grow to this size might be in the
next 20years and so want to decrease it to 100 MB ( the DB back up file is
only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB files.
I could not find any option anywhere in help to do this. When i enter a value
of 100 MB in space allocated under the DataFile properties Tab it gives me a
message "file size cannot be decreased, it can only be increased.". Please
tell me if there is any way to do this.
thanks
> i had set the size of a new database to 5 GB and transactional log file to
50
> MB. Now i realized that the database would grow to this size might be in
the
> next 20years and so want to decrease it to 100 MB ( the DB back up file is
> only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB
files.
> I could not find any option anywhere in help to do this. When i enter a
value
> of 100 MB in space allocated under the DataFile properties Tab it gives me
a
> message "file size cannot be decreased, it can only be increased.". Please
> tell me if there is any way to do this.
Hi!
Do please check the DBCC SHRINKFILE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||I suggest you do this from Query Analyzer using the DBCC SHRINKFILE command (which is documented in
Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:F8D6293E-7949-4E1F-B68C-B27E912164C1@.microsoft.com...
>i had set the size of a new database to 5 GB and transactional log file to 50
> MB. Now i realized that the database would grow to this size might be in the
> next 20years and so want to decrease it to 100 MB ( the DB back up file is
> only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB files.
> I could not find any option anywhere in help to do this. When i enter a value
> of 100 MB in space allocated under the DataFile properties Tab it gives me a
> message "file size cannot be decreased, it can only be increased.". Please
> tell me if there is any way to do this.
> thanks
>

Decrease the size of a Sql 2000

i had set the size of a new database to 5 GB and transactional log file to 5
0
MB. Now i realized that the database would grow to this size might be in the
next 20years and so want to decrease it to 100 MB ( the DB back up file is
only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB files
.
I could not find any option anywhere in help to do this. When i enter a valu
e
of 100 MB in space allocated under the DataFile properties Tab it gives me a
message "file size cannot be decreased, it can only be increased.". Please
tell me if there is any way to do this.
thanks> i had set the size of a new database to 5 GB and transactional log file to
50
> MB. Now i realized that the database would grow to this size might be in
the
> next 20years and so want to decrease it to 100 MB ( the DB back up file is
> only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB
files.
> I could not find any option anywhere in help to do this. When i enter a
value
> of 100 MB in space allocated under the DataFile properties Tab it gives me
a
> message "file size cannot be decreased, it can only be increased.". Please
> tell me if there is any way to do this.
Hi!
Do please check the DBCC SHRINKFILE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||I suggest you do this from Query Analyzer using the DBCC SHRINKFILE command
(which is documented in
Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:F8D6293E-7949-4E1F-B68C-B27E912164C1@.microsoft.com...
>i had set the size of a new database to 5 GB and transactional log file to
50
> MB. Now i realized that the database would grow to this size might be in t
he
> next 20years and so want to decrease it to 100 MB ( the DB back up file is
> only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB fil
es.
> I could not find any option anywhere in help to do this. When i enter a va
lue
> of 100 MB in space allocated under the DataFile properties Tab it gives me
a
> message "file size cannot be decreased, it can only be increased.". Please
> tell me if there is any way to do this.
> thanks
>

Decrease the size of a Sql 2000

i had set the size of a new database to 5 GB and transactional log file to 50
MB. Now i realized that the database would grow to this size might be in the
next 20years and so want to decrease it to 100 MB ( the DB back up file is
only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB files.
I could not find any option anywhere in help to do this. When i enter a value
of 100 MB in space allocated under the DataFile properties Tab it gives me a
message "file size cannot be decreased, it can only be increased.". Please
tell me if there is any way to do this.
thanks> i had set the size of a new database to 5 GB and transactional log file to
50
> MB. Now i realized that the database would grow to this size might be in
the
> next 20years and so want to decrease it to 100 MB ( the DB back up file is
> only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB
files.
> I could not find any option anywhere in help to do this. When i enter a
value
> of 100 MB in space allocated under the DataFile properties Tab it gives me
a
> message "file size cannot be decreased, it can only be increased.". Please
> tell me if there is any way to do this.
Hi!
Do please check the DBCC SHRINKFILE command in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||I suggest you do this from Query Analyzer using the DBCC SHRINKFILE command (which is documented in
Books Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:F8D6293E-7949-4E1F-B68C-B27E912164C1@.microsoft.com...
>i had set the size of a new database to 5 GB and transactional log file to 50
> MB. Now i realized that the database would grow to this size might be in the
> next 20years and so want to decrease it to 100 MB ( the DB back up file is
> only 20MB) and set it to "Autmatically Grow File" for both MDB and LDB files.
> I could not find any option anywhere in help to do this. When i enter a value
> of 100 MB in space allocated under the DataFile properties Tab it gives me a
> message "file size cannot be decreased, it can only be increased.". Please
> tell me if there is any way to do this.
> thanks
>

Debugging Log Files...

I'm having trouble with a few subscriptions getting emailed.
They are showing "email sent" in their status.
I remember a while ago, finding a post about a log file I can
read that showed transactions and their error or success messages,
but I can't find it now. I didn't have to look in the database, it was a
physical file.
Can anyone tell me where the transaction logs for RS 2000 are?Hi isideveloper,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: you want to know
where are the log files of Reporting Services 2000. If I misunderstood your
concern, please feel free to let me know.
By default, the trace log files of Reporting Servicec 2000 are localed at
\Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles
folder.
Reporting Services provides three trace log files, they are:
ReportServerService_<timestamp>.log Trace log for the Report
Server Windows service and Web service.
ReportServerWebApp_<timestamp>.log Trace log for Report Manager.
ReportServer_<timestamp>.log Trace log for the report server engine.
To resolve your subscription issue, please check these log files and post
the error message here. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Debugging a stored procedure

When I try to debug stored procedure in SQL Server 2000 Query Analyzer I got error message

SP debugging may not work propertly if you log as 'Local system account' while SQL Server is configured to run as service. You can open Event Viwer to see details ...

After this msgbox I press F5 to start and got error

Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on COMPAQ-NX6325\PRERADA_MESA (Error = 0x800401f3). Ensure that client-side components, such as SQLLE.DLL, are installed and registered on COMPAQ-NX6325. Debugging disabled for connection 55.

What I have to do to work with debugerWhen I try to debug stored procedure in SQL Server 2000 Query Analyzer I got error message

SP debugging may not work propertly if you log as 'Local system account' while SQL Server is configured to run as service. You can open Event Viwer to see details ...

After this msgbox I press F5 to start and got error

Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on COMPAQ-NX6325\PRERADA_MESA (Error = 0x800401f3). Ensure that client-side components, such as SQLLE.DLL, are installed and registered on COMPAQ-NX6325. Debugging disabled for connection 55.

What I have to do to work with debuger|||try this code in sql new query editor & execute

sp_Configure 'clr enabled',1
Go
Reconfigure
Go|||This belongs in the SQL Server forum, so this thread will be moved.

moderator|||Please do not double post.


Changed title and merged duplicate threads.