@@error for dynamic SQL

Does anyone know of a simple, correct way to retrieve the value of @.@.error from dynamic SQL? I can't use:SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''

EXECUTE (@.cCmd)
SELECT @.@.error -- only shows locally reported error

EXECUTE sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported errorI can get the error from a local procedure (one run on this box) using the first example, or on a remote server (via RPC) using the second, but not both. Any suggestions?

-PatPI'm confused as to what your expected output should be since I'm getting errors all over the place (typical results for me).

QUERY:
Set Concat_Null_Yields_Null Off

Declare @.cCmd nvarchar(4000)
Declare @.cServer nvarchar, @.cLogin nvarchar
, @.iError int

SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''

Print @.cCmd + N' << The Command' + nchar(10)

EXECUTE (@.cCmd)
SELECT @.@.error As 'Error' -- only shows locally reported error

EXECUTE @.iError = sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported error

Select @.iError

RESULTS
EXECUTE @.i = [].master.dbo.sp_password NULL, 'junque', '' << The Command

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Error
----
1038

(1 row(s) affected)

Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.

----
170

(1 row(s) affected)

What value do you expect for the error codes?|||I'm attempting to dynamically build a password change request, that will be executed on some arbitrary number of servers determined by values in a table. The code that I posted assumes that your current login has the ability to dynamically execute sp_password for a login on both your local server and all of the remote servers.

When it executes sp_password on the local server, the first example works well and the error code is available via the SELECT. When it executes sp_password on a remote server (via RPC, Remote Procedure Call), then the second example works well, but I haven't found any reliable way to retrive the @.@.error result.

At the moment, I'm thinking that I'll just use the 0/1 return code from sp_password and wait until I have time to create an extended stored procedure (or the problem goes away) for a more elegant fix.

-PatP|||Thanks - now I get it.

The only way I could find around this is to put a wrapper around sp_password by creating my_sp_password via hacking sp_password - one of the flaws with this approach is that when sp_password changes, you have to also potentially change the wrapper (my_sp_password).

If you choose to use this hack, you may want to comment out/remove the RAISERRORs. Note: I did not test this on linked servers.

WRAPPER
create procedure my_sp_password
@.old sysname = NULL, -- the old (current) password
@.new sysname, -- the new password
@.loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @.self int, @.error int
select @.self = CASE WHEN @.loginame is null THEN 1 ELSE 2 END
, @.error = -1

-- RESOLVE LOGIN NAME
if @.loginame is null
select @.loginame = suser_sname()

-- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
IF (not is_srvrolemember('securityadmin') = 1)
AND not @.self = 1
begin
dbcc auditevent (107, @.self, 0, @.loginame, NULL, NULL, NULL)
raiserror(15210,-1,-1)
select @.error = 15210
return (@.error)
end
ELSE
begin
dbcc auditevent (107, @.self, 1, @.loginame, NULL, NULL, NULL)
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@.@.trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
select @.error = 15002
return (@.error)
end

-- RESOLVE LOGIN NAME (disallows nt names)
if not exists (select * from master.dbo.syslogins where
loginname = @.loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@.loginame)
select @.error = 15007
return (@.error)
end

-- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
if (@.self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
(SELECT * FROM master.dbo.syslogins WHERE loginname = @.loginame and isntname = 0
AND sysadmin = 1) )
SELECT @.self = 1

-- CHECK OLD PASSWORD IF NEEDED --
if (@.self = 1 or @.old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @.loginame and
( (@.old is null and password is null) or
(pwdcompare(@.old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
select @.error = 15211
return (@.error)
end

-- CHANGE THE PASSWORD --
exec sp_configure 'allow', 1
Reconfigure
set @.error = @.@.error

if @.error = 0
begin
-- update master.dbo.sysxlogins
-- set password = convert(varbinary(256), pwdencrypt(@.new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
-- where name = @.loginame and srvid IS NULL
exec @.error = sp_password @.old, @.new, @.loginame
end

-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
if @.error = 0
begin
exec('use master grant all to null')
set @.error = @.@.error
end

-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @.error <> 0
return (@.error)
raiserror(15478,-1,-1)
set @.error = 15478
return (@.error) -- sp_password

EXECUTION
Declare @.error int
Select @.error = -1
exec @.error = my_sp_password 'fred', 'ted', 'sam'
Select @.error|||Or

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE ProcessLog(Spid int, ProcessName varchar(255), Row_Count int, Error int, Return_Code int, Add_Dt datetime DEFAULT GetDate())
GO

DECLARE @.cCmd varchar(8000), @.cServer sysname, @.cLogin sysname
SELECT @.cServer = @.@.SERVERNAME, @.cLogin = SYSTEM_USER

SELECT @.cCmd = 'DECLARE @.i int' + CHAR(13)
+ 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''test'', '''
+ @.cLogin + ''''+ CHAR(13)
+ 'INSERT INTO ProcessLog(Spid, ProcessName, Row_Count, Error, Return_Code)' + CHAR(13)
+ 'SELECT ' + CONVERT(varchar(15),@.@.SPID) + ', ''sp_password'''+', @.@.ROWCOUNT, @.@.ERROR, @.i' + CHAR(13)

SELECT @.cCmd

EXECUTE (@.cCmd)

SELECT * FROM ProcessLog o
WHERE Spid = @.@.SPID
AND Add_Dt IN ( SELECT MAX(Add_Dt)
FROM ProcessLog i
WHERE i.Spid = o.Spid)
GO

SET NOCOUNT OFF
DROP TABLE ProcessLog
GO|||Brett's example works like my example 1 for the local server, and would make Rube Goldberg giddy with glee in the process. I really want something that works equally well for both local and remote without a whole lot of work to make it fly.

-PatP|||How does your first one work?

Doesn't it return an error?

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.

?

I didn't test it remotely, are you saying it won't work?|||What I posted is actually an excerpt from a much larger script, only to show the general idea of what I'm doing... I didn't intend for anyone to execute it "as is", but I should have been more clear about that.

The first bit of logic works nicely when the code is being executed on the local server (in other words, when @.cServer = @.@.servername like in your example). The second bit of code works when the dynamic SQL generates an RPC, and it can be elaborated on to return the actual @.@.error value by following the SELECT with a SET command in the dynamic SQL. Because the code is trapped at two different layers within the SQL Engine, and is reported differently depending on who catches what, I can't think of a single, coherant way to trap the actual error code (15087 if I remember correctly) for both cases unless I construct wildly different bits of SQL and test to see which SQL I should use.

As I said, I think this is a design issue in SQL Server that I can't readily fix. I think that the proper answer is to just break down and code it as an extended stored procedure, where I can "put a leash on the monster" and stop fighting it. I just don't have the luxury of the time (probably around 8 hours to write and test, without the enhancements I'd really like to add) needed for that kind of fix at the moment.

-PatP|||DECLARE @.j INT
CREATE TABLE #temp ( id INT IDENTITY (1,1), result VARCHAR(255) )
INSERT INTO #temp (result)
EXEC @.j = master.dbo.xp_cmdshell 'osql -E -Q "EXIT(EXECUTE master.dbo.sp_password NULL, ''junque'', ''test3''; )"'
SELECT @.j
IF @.j = -100
BEGIN
SELECT SUBSTRING(result, CHARINDEX('Msg ', result) + 4, (CHARINDEX(' ',result,CHARINDEX('Msg ', result) + 5) - CHARINDEX('Msg ', result) - 5))
FROM #temp
WHERE id = 1
END

DROP TABLE #temp

NOTE: I don't have a remote server to test it with at home.

Tim S|||I don't think that the query from TimS will get me much further than what I've got right now, but it gives me an idea! I think he's resolved the issue of how to work around the distinctly different way that MS-SQL handles errors on the local SQL Server versus remote SQL servers by using osql.exe to put all servers on a local footing. I'll have to experiment a bit when I get back to a machine with MS-SQL available, but I think it might work to do something like:SET @.cmd = 'osql -E -Q "EXECUTE sp_password NULL, ''abcde'', '''
+ @.cLogin + ''' EXIT(SELECT @.@.error)" -s ' + @.cServer'

EXECUTE @.iRC = master.dbo.xp_cmdshell @.cCmdTo simulate the remote server, just create a second instance of MS-SQL on your machine. As long as the network isn't specifically a part of your test, a second instance works nicely for whatever I've needed to test.

-PatP

::FN_DBLOG() - Problem in retreiving [row data]

Subj: ::FN_DBLOG() - Problem in retreiving [row data]
Hello
While attempting to retrieve [row data] column using ::FN_DBLOG
an empty '0x' value is passed back:
select [Current Lsn], [row data] from ::fn_dblog(null,null)
The retrived record does contain row data. It can be seen when using
DBCC LOG.
However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
a standard SQL query. In addition - DBCC seems to be a more resource
consuming
piece.
Please do not propse a 3rd party software solution.
I need the [row data] 'as is' in its very basic binary raw format and
::FN_DBLOG() fits exactly to my needs.
Any help will be appreciated.
Regards
Hillel.Both fn_dblog() and dbcc log are undocumented and therefore unsupported
features of SQL Server, so in all cases you are on you own.
If you can send me a repro I will look at this. What are you trying to do
with that information anyhow?
GertD@.SQLDev.Net
"Hillel" <hillel.eilat@.gmail.com> wrote in message
news:1144768876.281792.244330@.e56g2000cwe.googlegroups.com...
> Subj: ::FN_DBLOG() - Problem in retreiving [row data]
> Hello
> While attempting to retrieve [row data] column using ::FN_DBLOG
> an empty '0x' value is passed back:
> select [Current Lsn], [row data] from ::fn_dblog(null,null)
> The retrived record does contain row data. It can be seen when using
> DBCC LOG.
> However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
> a standard SQL query. In addition - DBCC seems to be a more resource
> consuming
> piece.
> Please do not propse a 3rd party software solution.
> I need the [row data] 'as is' in its very basic binary raw format and
> ::FN_DBLOG() fits exactly to my needs.
> Any help will be appreciated.
> Regards
> Hillel.
>sql

::FN_DBLOG - Retreiving [row data]

Subj: ::FN_DBLOG - Retreiving [row data]
Hello
While attempting to retrieve [row data] column using ::FN_DBLOG
an empty '0x' value is passed back.
The retrived record does contain row data. It can be seen when using DBCC LO
G.
However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
a standard SQL query. In addition - DBCC seems to be a more resource
consuming
piece.
Please do not propse a 3rd party software solution.
I need the [row data] 'as is' in its very basic binary raw format and ::
FN_DBLOG() fits exactly to my needs.
Any help will be appreciated.
Regards
Hillel.Hi hillel,
I don't know exactly what you are looking for but launching this you can
recover the log info:
SELECT * FROM ::fn_dblog(null,null)
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"hillel" wrote:

> Subj: ::FN_DBLOG - Retreiving [row data]
> Hello
> While attempting to retrieve [row data] column using ::FN_DBLOG
> an empty '0x' value is passed back.
> The retrived record does contain row data. It can be seen when using DBCC
LOG.
> However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
> a standard SQL query. In addition - DBCC seems to be a more resource
> consuming
> piece.
> Please do not propse a 3rd party software solution.
> I need the [row data] 'as is' in its very basic binary raw format and ::
> FN_DBLOG() fits exactly to my needs.
> Any help will be appreciated.
> Regards
> Hillel.
>|||Thank you Enric.
My question is very specific.
In SQL 2000 - there are 2 interfaces by which I can access the transaction
LOG:
DBCC LOG is not a very convinient API in many aspects.
::FN_DBLOG() is much more tempting but - surprisingly - it does not display
the [row data] columns - as seen below:
SELECT [Current LSN],[Transaction ID],[Object Name],[row data]
FROM ::fn_dblog(null,null) where operation='LOP_INSERT_ROWS'
0000000b:0000018b:0002 0000:000005bf dbo
.jobs (1977058079) 0x
0000000b:00000190:0002 0000:000005c1 dbo
.jobs (1977058079) 0x
0000000b:00000190:0003 0000:000005c1 dbo
.jobs (1977058079) 0x
There is data associated with these records - as seen below:
dbcc log(DB5,3,logrecs,'LOP_INSERT_ROWS')
0000000b:0000018b:0002 ...0000:000005bf... dbo.jobs (1977058079)...
0x3000080025004749040000010030004261636B
75705F313931393139313931393139313931
3931393139313931393139
Did I miss something in the ::FN_DBLOG() invocation - or- what...?
Thanks
Hillel.
"Enric" wrote:
> Hi hillel,
> I don't know exactly what you are looking for but launching this you can
> recover the log info:
> SELECT * FROM ::fn_dblog(null,null)
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "hillel" wrote:
>

.net connectivity and SQL connectivity problems

Hi,
Have a quick question. Having a problem connecting from the IIS server
(windows 2000 sp3) which hosts the asp.net application to retrieve data from
an SQL server (windows nt 4, sql 7). The error keeps coming up SQL server
does not exist or access denied. I've checked the permissions, etc and it
seems to be fine. I used query analyzer from the windows 2000 (has
enterprise connectivity tools) machine and it works. Here is the connection:
protected string myAdminConnectionString = "Provider=dbmssocn;Data
Source=PIMZZ00;Initial Catalog=PIM;User ID=temp;Password=temp";
also tried
protected string myAdminConnectionString = "Data Source=192.168.1.2;Initial
Catalog=PIM;User ID=temp;Password=temp";
I tried installing SQL server on the 2000 machine (had an extra license) but
MMC is getting an error. Has anyone seen this error before?
Application popup: mmc - Application Error: The instructions at 0x77abfcf5
referenced memory at 0x0000000" The memory could not be read.
In the event viewer it came up with:
The description for Event ID 19011 in Source (MSSQLServer) cannot be found.
The local computer may not have the necessary registry information or message
DLL files to display messages from a remote computer. The following
information is part of the event (SpnRegister): Error 1355.
Thanks - Zack
Hi Zack,
Two thoughts on this. One make a test UDL to test connectivity through
the provider. If this works, then open the UDL with Notepad and use the
same connection string in your application. If it fails, then make a
network trace from the client to capture the traffic.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

.mdf file

Your all going to laugh at me. I know NOTHING about SQL
Server. I have an application which is using a SQL
database I set-up to retrieve data. I noticed I have
a .mdf file (Master database File?) and .ldf (Lock file
I assume?) that are huge. 32GB and 15GB respectively.
Huge considering that the actual data in the database is
only about 1-1.5GB. (I can run it in MS Access no
problem). What exactly is the .mdf file? What is its
function? AND most importantly, can I shrink it somehow?
How would it get SO big? I appreciate any help a lot!
Thanks!
Hi,
What exactly is the .mdf file , .LDF file?
MDF - Primary data file
NDF - Secondary data file
LDF - Transaction log file
Functions:-
MDF
It stores all the SQL Server Objects , data , system catelogs,security and
.....
LDF
Server uses the transaction log of each database to recover transactions.
The transaction log is a serial record of all modifications
that have occurred in the database as well as the transaction that performed
each modification
Can I shrink it somehow?
Yes , you can shrink the database.
Steps:
1. Take a database backup and transaction log backup
2. Execute the below command in Query analyzer to identify the MDF and LDF
file info
use <dbname>
go
sp_helpfile
3. Use the name column (Logical name for MDF and LDF) to shrink the
database. Use the below command
dbcc shrinkfile('logical_MDF_name',size_to_shrink_in_MB ')
go
dbcc shrinkfile('logical_LDF_name',size_to_shrink_in_MB ')
How would it get SO big?
No idea about MDF file, Execute the command sp_spaceused to identify the
actual database size. LDF can grow if you have any
maintenance activies like reindexing, batch operation ....
Thanks
Hari
MCDBA
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:1460901c444bf$40824ce0$a101280a@.phx.gbl...
> Your all going to laugh at me. I know NOTHING about SQL
> Server. I have an application which is using a SQL
> database I set-up to retrieve data. I noticed I have
> a .mdf file (Master database File?) and .ldf (Lock file
> I assume?) that are huge. 32GB and 15GB respectively.
> Huge considering that the actual data in the database is
> only about 1-1.5GB. (I can run it in MS Access no
> problem). What exactly is the .mdf file? What is its
> function? AND most importantly, can I shrink it somehow?
> How would it get SO big? I appreciate any help a lot!
> Thanks!
|||The master database is the system database for SQL Server. It should not be
that big. It appears that the application (or something/someone else) has
been creating tables and loading data into this database. You probably
need to determine what is in it and clean out as much of it as possible and
relocate it ot a different user defined database.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I just re-read your post and if the mdf file is not related to the master
database you can disregard my last post.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Joe
Just to add...
It is worth spending some time reading Books Online, which will come on the
SQL Server CD or downloadable from
http://www.microsoft.com/sql/techinf...2000/books.asp
John
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:1460901c444bf$40824ce0$a101280a@.phx.gbl...
> Your all going to laugh at me. I know NOTHING about SQL
> Server. I have an application which is using a SQL
> database I set-up to retrieve data. I noticed I have
> a .mdf file (Master database File?) and .ldf (Lock file
> I assume?) that are huge. 32GB and 15GB respectively.
> Huge considering that the actual data in the database is
> only about 1-1.5GB. (I can run it in MS Access no
> problem). What exactly is the .mdf file? What is its
> function? AND most importantly, can I shrink it somehow?
> How would it get SO big? I appreciate any help a lot!
> Thanks!

.mdf file

Your all going to laugh at me. I know NOTHING about SQL
Server. I have an application which is using a SQL
database I set-up to retrieve data. I noticed I have
a .mdf file (Master database File?) and .ldf (Lock file
I assume') that are huge. 32GB and 15GB respectively.
Huge considering that the actual data in the database is
only about 1-1.5GB. (I can run it in MS Access no
problem). What exactly is the .mdf file? What is its
function? AND most importantly, can I shrink it somehow?
How would it get SO big? I appreciate any help a lot!
Thanks!Hi,
What exactly is the .mdf file , .LDF file?
MDF - Primary data file
NDF - Secondary data file
LDF - Transaction log file
Functions:-
MDF
--
It stores all the SQL Server Objects , data , system catelogs,security and
....
LDF
--
Server uses the transaction log of each database to recover transactions.
The transaction log is a serial record of all modifications
that have occurred in the database as well as the transaction that performed
each modification
Can I shrink it somehow?
Yes , you can shrink the database.
Steps:
1. Take a database backup and transaction log backup
2. Execute the below command in Query analyzer to identify the MDF and LDF
file info
use <dbname>
go
sp_helpfile
3. Use the name column (Logical name for MDF and LDF) to shrink the
database. Use the below command
dbcc shrinkfile('logical_MDF_name',size_to_shrink_in_MB')
go
dbcc shrinkfile('logical_LDF_name',size_to_shrink_in_MB')
How would it get SO big?
No idea about MDF file, Execute the command sp_spaceused to identify the
actual database size. LDF can grow if you have any
maintenance activies like reindexing, batch operation ....
Thanks
Hari
MCDBA
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:1460901c444bf$40824ce0$a101280a@.phx.gbl...
> Your all going to laugh at me. I know NOTHING about SQL
> Server. I have an application which is using a SQL
> database I set-up to retrieve data. I noticed I have
> a .mdf file (Master database File?) and .ldf (Lock file
> I assume') that are huge. 32GB and 15GB respectively.
> Huge considering that the actual data in the database is
> only about 1-1.5GB. (I can run it in MS Access no
> problem). What exactly is the .mdf file? What is its
> function? AND most importantly, can I shrink it somehow?
> How would it get SO big? I appreciate any help a lot!
> Thanks!|||The master database is the system database for SQL Server. It should not be
that big. It appears that the application (or something/someone else) has
been creating tables and loading data into this database. You probably
need to determine what is in it and clean out as much of it as possible and
relocate it ot a different user defined database.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I just re-read your post and if the mdf file is not related to the master
database you can disregard my last post.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Joe
Just to add...
It is worth spending some time reading Books Online, which will come on the
SQL Server CD or downloadable from
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
John
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:1460901c444bf$40824ce0$a101280a@.phx.gbl...
> Your all going to laugh at me. I know NOTHING about SQL
> Server. I have an application which is using a SQL
> database I set-up to retrieve data. I noticed I have
> a .mdf file (Master database File?) and .ldf (Lock file
> I assume') that are huge. 32GB and 15GB respectively.
> Huge considering that the actual data in the database is
> only about 1-1.5GB. (I can run it in MS Access no
> problem). What exactly is the .mdf file? What is its
> function? AND most importantly, can I shrink it somehow?
> How would it get SO big? I appreciate any help a lot!
> Thanks!

.mdf file

Your all going to laugh at me. I know NOTHING about SQL
Server. I have an application which is using a SQL
database I set-up to retrieve data. I noticed I have
a .mdf file (Master database File?) and .ldf (Lock file
I assume') that are huge. 32GB and 15GB respectively.
Huge considering that the actual data in the database is
only about 1-1.5GB. (I can run it in MS Access no
problem). What exactly is the .mdf file? What is its
function? AND most importantly, can I shrink it somehow?
How would it get SO big? I appreciate any help a lot!
Thanks!Hi,
What exactly is the .mdf file , .LDF file?
MDF - Primary data file
NDF - Secondary data file
LDF - Transaction log file
Functions:-
MDF
--
It stores all the SQL Server Objects , data , system catelogs,security and
....
LDF
--
Server uses the transaction log of each database to recover transactions.
The transaction log is a serial record of all modifications
that have occurred in the database as well as the transaction that performed
each modification
Can I shrink it somehow?
Yes , you can shrink the database.
Steps:
1. Take a database backup and transaction log backup
2. Execute the below command in Query analyzer to identify the MDF and LDF
file info
use <dbname>
go
sp_helpfile
3. Use the name column (Logical name for MDF and LDF) to shrink the
database. Use the below command
dbcc shrinkfile('logical_MDF_name',size_to_sh
rink_in_MB')
go
dbcc shrinkfile('logical_LDF_name',size_to_sh
rink_in_MB')
How would it get SO big?
No idea about MDF file, Execute the command sp_spaceused to identify the
actual database size. LDF can grow if you have any
maintenance activies like reindexing, batch operation ....
Thanks
Hari
MCDBA
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:1460901c444bf$40824ce0$a101280a@.phx
.gbl...
> Your all going to laugh at me. I know NOTHING about SQL
> Server. I have an application which is using a SQL
> database I set-up to retrieve data. I noticed I have
> a .mdf file (Master database File?) and .ldf (Lock file
> I assume') that are huge. 32GB and 15GB respectively.
> Huge considering that the actual data in the database is
> only about 1-1.5GB. (I can run it in MS Access no
> problem). What exactly is the .mdf file? What is its
> function? AND most importantly, can I shrink it somehow?
> How would it get SO big? I appreciate any help a lot!
> Thanks!|||The master database is the system database for SQL Server. It should not be
that big. It appears that the application (or something/someone else) has
been creating tables and loading data into this database. You probably
need to determine what is in it and clean out as much of it as possible and
relocate it ot a different user defined database.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I just re-read your post and if the mdf file is not related to the master
database you can disregard my last post.
Rand
This posting is provided "as is" with no warranties and confers no rights.