How to shrink log file.

Hi, I have a database in SQL 2000 server whose
Transaction log file size is over 5GB but dats file size
just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
is not frequently used and I wanted to shrink the size to
save some disk space.In the properties of this log
file, 'unrestricted growth' is enabled.
Could some one help me to do it.
Thanks and regards, JohnHi,
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
MCDBA
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||backup transaction log
and then run dbcc shrinkfile(x_log, TRUNCATEONLY)
The truncateonly causes the unused space to be released to the Operating
System (Check oout BOL for dbcc shrinkfile)
Olu Adedeji
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John

How to shrink log file.

Hi, I have a database in SQL 2000 server whose
Transaction log file size is over 5GB but dats file size
just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
is not frequently used and I wanted to shrink the size to
save some disk space.In the properties of this log
file, 'unrestricted growth' is enabled.
Could some one help me to do it.
Thanks and regards, JohnHi,
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
MCDBA
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||backup transaction log
and then run dbcc shrinkfile(x_log, TRUNCATEONLY)
The truncateonly causes the unused space to be released to the Operating
System (Check oout BOL for dbcc shrinkfile)
--
Olu Adedeji
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||Yes it worked. Thanks a lot.
>--Original Message--
>Hi, I have a database in SQL 2000 server whose
>Transaction log file size is over 5GB but dats file size
>just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
>is not frequently used and I wanted to shrink the size
to
>save some disk space.In the properties of this log
>file, 'unrestricted growth' is enabled.
>Could some one help me to do it.
>Thanks and regards, John
>.
>|||>>Hi, I have a database in SQL 2000 server whose
>>Transaction log file size is over 20GB but mdf file size
>>just 4GB. That data base
>>is frequently used and I wanted to shrink the size
>to
>>save some disk space.Moreover I want to delete the old
transactions of this particular log file. pls guide me how
to do this.
Regards
Triveni|||Hi!!
I have a database of around 116GB and somehow I lost the
> log file now if I want to do any transaction, it simply
> says log file is not available as I lost the log
> file.Kindly help me to get rid of this problem
any help would be highly appreciated.
>
>--Original Message--
>Yes it worked. Thanks a lot.
>>--Original Message--
>>Hi, I have a database in SQL 2000 server whose
>>Transaction log file size is over 5GB but dats file size
>>just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
>>is not frequently used and I wanted to shrink the size
>to
>>save some disk space.In the properties of this log
>>file, 'unrestricted growth' is enabled.
>>Could some one help me to do it.
>>Thanks and regards, John
>>.
>.
>

How to set up an .mdf db?

Would someone please take a minute to give me a step by step how to add an .mdf db to a web app(VS 2k5 Pro with SSE)?

Only one app in the project intended as a complete sample solution to be zipped and distributed to people who would have a similar VS setup. The idea is the recipients to be able to open the .mdf (or the pages using it) right out of the box without having to go to hell and back. The .mdf included in the App_Data and the connection specified in the web.config. All ready to just unzip and go.

Obviously, it would have to be sql authentication.
Say user: johndoe/ pw: 123456

Amazing how the db connection process still has a way of bringing otherwise intelligent people to their knees.

TIA

Step 1 - New web Site :-)

Step 2 - Open Server Explorer (Ctrl W,L)

Step 3 - Right Click Data Connections and Choose Add Connection

Step 4 - Change the Data Source to be a Microsoft Sql Server Database File using the Change button

Step 5 - Use the Browse button to create a new MDF file in your App_Data folder.

Step 6 - Now you should get a dialog asking if you want to create the file. Press Yes and you should now have a Data Connection in server explorer which you can use.

Hope that helps

|||

Forgot to say that between step 5 and 6 you should select SQL authentication instead of windows authentication.

|||

Hi James,

Thanks for taking the time. I also thought that is what it takes before I started playing with SSE.
The steps you listed only work for windows authentication mode. If I select SQL authentication I get an arror:

Login failed for user '<mylogin>'. The user is not associated with a trusted SQL Server connection.

I tried all kinds of<mylogin>values Administrator, the current login, totally nonexistent names... They all give the same error. The idea is to put in a username/pw that have nothing to do with my machine. Isn't that the point of file based db (unless we are expected to deploy on the dev machine)? Anyway, it rejects all of them indiscriminately.

Back in sql 2k, this error was associated with not setting up globally the authentication mode of the server instance to either mixed or just sql.

With SSE there is no concept of multiple instances or global server settings for authentication, like with the full versions. I had installed VS a couple of months ago and there is no place in the options that shows the current configuration. So I uninstalled SSE just to go through the install all over again. Well, it just rolled along automatically. I didn't see anywhere during the install to set up the server. I assumed that was being done at the file level, as the GUI suggests.

Something just doesn't add up.

|||

Rob

All becomes clear. The default setup for VS2005 Express is to only support windows authentication. The setup doesn't allow you to change this during the graphic stage, you have to start setup with the correct command switch. The good news is that you can change it to mixed mode with a bit of registry hacking.

See thisknowledge base. Hopefully that should get you on the way.

Cheers

|||

Thanks James.

This only partly solves the problem. The recipients of the sample project will also face an issue connecting.

Am I misunderstanding the puspose of .mdf files? They do not seem to be transportable databases from one machine to another like Access. I can't expect everyone I send a sample project to hack their registry, just to try the sample. Most installs out there are exactly like mine - done from the setup media, thus default windows authentication mode. Why did they even bother showing the other modes in the GUI?

If I have to hack the registry to get a basic functionality I am by definition misusing the ptoduct. What is the right way to accomplish this, what I thought was a simple and popular, task? Namely to send a one page example along with a db all packaged together?

Using Access instead is not the same. Many features are different.Embedding SSE is a huge job too, and not really pertinent to web apps anyway.

|||

I only mentioned the registry hack for your machine.

If you install SQL Server Express with the switch SECURITYMODE=SQL then it will be in Mixed mode authentication.

Then you can ship the mdf file. This article has a good story about how to embed Server 2005 with your app -http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/EmSQLExCustApp.asp

Cheers

James

|||

Yes, I am aware of this article about embedding. As I said it is not straight forward and it does not pertain to the simple scenario I describe.

OK. Clean new xp sp2 OS install.
Clean new NAMED instance SSE install with mixed authentication mode specified at setup.

I still get the exact same error, if I try to create a new .mdf with sql authenitcation.

What gives?

|||

1) New website.

2) Right click "App_data" in solution explorer.

3) Click "Add new item"

4) Click "SQL Database".

5) Click "Ok".

Is there something wrong with this particular setup that isn't working for you?

If you then drag a SqlDataSource to your webpage, and select the connection property, one of the entries in the dropdown should be "database.mdf", select that. If you then go to the .aspx source, you should see that the connection string it is using is:

ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"

There is no mention of the machine name, or the project directory in there, and you can just .zip the project folder, give it to someone else, and they can use it immediately.

|||

ok. So an .mdf with just windows authentication works on any machine it is on.

I still have to digest the sucurity implications, but for now insecure is exactly what I need so ticket closed!

Thanks to everybody.

|||

I recently re-installed visual studio 2005. Since that time I have not been able to connect to .mdf files or create them in the App_Data directory. I receive the "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." error. Anybody have any ideas as to what is causing this?

Thanks