Hello -
I have over 100 MSSQL Databases on my SQL SERVER. How do I restrict
all the MSSQL databases and its transaction logs to 100 MB.
Can someone help me with any script which will do that.
Thanks,
Rubal Jain
www.Rubal.net
hi all,
I am using SQL server 2005.The Log file size increased to 40GB,so I detach the DB and delete the log & created new log file.Can I restrict the growth.I mean if the file size become 1000MB,I need to clear the log file.Previously it was Enabled the autogrowth 10% & unrestricted File size,Actually I modified to restricted file size 1000MB.is it work? can I know restricted file size 1000MB what will happen?I mean , is it clear the log after reaching 1000MB?please advice.
Thanks in advance
with regards,
leo
Here is what I need to do, maybe someone could tell me what to do.
I need to create a login (I got that part) and then limit that login to only be able to see one of the databases on the server. So this is what I'm looking for.
user1 logs in to the server
user1 can only see the database I want them to see
and can not see anything else, no msdb, no tempdb, no security, no management etc.
I also need this user to be read only (only able to run select statements)
and this user will not be the owner of the database
Please help i'm pulling my hair out
Thanks Ahead of time
Hello
I have an MSSQL server where I would like to create a couple of databases, create one user pr. DB, and give them db_owner permissions.
Giving a user db_owner permissions allows the user to make a backup of the database, which is a good thing and I would like it to stay that way.
My problem is that I don't know how to restrict the user from saving the backup to any directory or filename that he wants to.
He can not get out of the directories that the user MSSQL is running under has access to. But he can overwrite another users backupfile or placing the file in the MSSQL-programfolder.
The users is using SQL Server authentication.
Can I restrict the backup procedure to save to only one file or in one directory?
What is the best practice regarding backup if you give your users db_owner permissions?
We can restrict users from accessing the databases from the security. But is there anyway , we can restrict users from seeing the available databases on the server, user can access and see the database he or she has access to other databases will not be visible?
View 13 Replies View Relatedhello,
I am using MS SQL db for my web application . I wanna know if there is any way to protect /secure database files from copied or used by any un authorised person.
Right now any one can copy the data base files(.data and .log) files from MS SQL's data folder, and can use it any where without any problem. But i would like to restrict any un authorised person to use it. Please let me if there is any way to avoide this.
Please help me in this problem
All suggesstions are welcome.
thank you.
-Archana
I have a single database and 5 user which use this database for DDL and DML operations.
The problem we are facing here is, every time a user need to do some work in this database he/she had to ask(manually) to rest of the 4 users whether they are doing some task over the same DB or not. Sometime due to communication gap one user open any transaction which creates a deadlock for another user to execute any query over the same tables in this DBI want to get rid of this problem by making it configurable from SQL Server part so that if any user which is already accessing this DB, so the other user don't get access to it, Â kind of Mutually exclusive behavior.
How to do this.
Dear Freinds..
I want to protect a field in a table...i want to restrict users to update the value in that field...by manually logging into that database...it can be updated only through the application...if any body manually update the field value....it has to be captured in log with old value....is it possible to do this sql server...if any of u says yes 'its possible' then :beer: other wise :eek:
Hello AllI am using SQL server 2000 as the backend of my application but don'twant my clients tobe able to view or edit the database tables, storedprocedures , view etc using enterprise manager or query analyser (orsimilar tools)How can this be done ?I searched a lot for this but unable to get the correct answer isthere anybody to give full solution regarding this.Please relpy me as early as possible
View 2 Replies View RelatedHi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
Hi,
I am using
exec sp_helpdb
go
dbcc sqlperf(logspace) for
getting database size and log size. Is this gives the correct
database size and log size or Is there any other way to get the logsize and database size by means of query analyzer.
Thanks in Advance.
Seenu. S
I'm getting this error while trying to insert records into a SQL Server Compact Edition database. I have pasted my connection string that was used when creating the database as well as for accessing that same database from my Windows application.
Thanks for any help any of you can give!
Data Source=OnTheGo.sdf;Encrypt Database=True;Password=<password>;Max Database Size=4091
Hello,
I am developing a smart device application with Visual Studio .Net 2005 and SQL Server Compact Edition database. And also using merge replication to synchronize the data from the mobile device to the SQL Server.
My database size is around 350MB. So when I am trying to synchronize this is the error message that I get.
" The database file is larger than the configured maximum database size. The setting takes effect on the first concurrent database connection only.[Required Max Database size ( in MB; 0 if unknown)=129].
I tried changing the Max database size in the connection string and my connection string looks as follows and still did not have any luck.
connstr= "Data Source=Storage CardItems.sdf;Max Database Size=500;"
Any help regarding this would be appreciated.
Thank you
.
hi
my database on remote server i cannot access directly.
i can access it only with query analyzer.
my log file size is 9mb but nothing in database. only few tables there so how i can reduce log file size with query.
thanks
Is it possible to get the SQL Server database size programmatically?
I have an WEB application in c# ASP NET, but I can found any information about.
Thanks for your attention
Freedeveloper
We have a SQL server database most time for read only because the data source comes from another database( which is not SQL server database, now it is about 2G). Every day we have a job running as following:
Step 0. extra the data from another database, and create plain text files for each table for BCP job.
Step 1. drop all of the index
Step 2. truncate all of the tables.
Step 3. BCP in all of the data from plain text file.
Step 4. create all of the index again
Step 5. shirnk the database.
Everything runs fine but the database grows 1 G from yesterday's database. I am sure we do not have so much data entry in one day.
Any one can give some suggestion?
I wonder if I need do shirnk the database or shirnk the data file before create the index.
How can know how much size for all of the index file?
Thank you very much.
Judy
I am a beginner with sql and I have been inputting data (txt files) to my database and now have approached 4GB in size and it will not let me expand any further on the primary file group. My 'boss' said that sql has no size. I am using desktop
View 4 Replies View RelatedIs there a stored procedure that returns the current size of a database, and the maximum size of a database whose growth property IS NOT set to unlimited?
TIA,
Chris
Just wondering if anyone knew the maximum size of a database that SQL Server 7 can handle.
joe
When I run sp_spaceused on my database it give me a total size of 258MB
The backup of this database reads 226MB
The Properties screen tells me that my data is a total of 278MB
I am running SQL 7.0 .
Why am I getting different sizes?
I have a simple question, why Am I running out of space with my current 3.5GB database, I am constantly expanding it, yet when I dump the database nightly the dump is just around 1.5GB?
View 1 Replies View RelatedNewbie here ....I started with a datbase of 90 megs...it now grew to 300 megs in 2 months...what is the best way to see the largest table or to find out why this database grew so fast? Thanks!
View 3 Replies View RelatedHi..
My database data file are over 2.1 Gbs now. can anyone tell me is my data file too big?? or not safe?? or there's a limite for MSSQL 2000. my last choice are move some data to another database. but i have consider the report i have to make. so i really dont want to do that. can anyone advice. thanks
Hi,
I have 30 servers and about 600 databases. I want to know the size of databases ( data files as will log files) both used and allocated space.
Can any one tell me the easiest way to get this information?
Thanks.
Hi all,
We are using sql6.5. srvpack 3.
I noticed that one of our database was maxed out, and increased the size of the device and then increased the size of the database.
when I checked the database it is still showing the database is maxed out. I did it twice, it still shows that the space has been used.
Can anyone please explain to me why this is happening and how to get the actual space measurement.
Thanks in advance.
After we ran sp_spaceused on one database, the size for the UNUSED column is a negative number -1324KB. Can someone explain how this could happen? I really appreciate.
View 1 Replies View RelatedHi,
I need to check the log size of my database periodically and send a notification if the size exceeds 8 GB. I want to set this up as job that runs every 30 minutes or so and page the DBA if it exceeds the above said limit. Can anyone give me the command which can be used to obtain the logsize into a variable. I am new to SQL and would appreciate your expert advise.
Thanks,
Marcus
Hi,
Someone can telle how with a query can i get the use size of all my db of akll my server.
I use the table sysfiles but is the total size of my file and not the use size.
Thanks a lot and happy new year.
Best regards.
Is there any shortest way to database size (data & log)
I refer to Article ID: 256650 (reduce log file size)
Quit a lot of step
I want to reduce physical occupied HDD size
Is there any way I can join all the information produced by this into a single output rather than multiple outputs
EXEC sp_MSforeachtable @command1=" EXEC sp_spaceused '?'"
Like this
Listing_Images43 16 KB8 KB8 KB0 KB
User 3 16 KB 8 KB 8 KB 0 KB
I want to fill a gridview in ASP but don't know how to deal with data produced through multiple queries
Hi All,
Will the database size decrease once I delete data from the audit tables?
I want how can i do to do capacity planning in sqlserver 2000.
Do you make scripts (vbs) who query the sysfiles tables and transfer the answer in excel ?
Please help me.
Phil